5 Ways to Compare Three Columns Using Vlookup in Excel

In this lesson, I’ll demonstrate 5 effective approaches on How to Compare Three different Columns in Excel Using VLOOKUP. These techniques will enable you to solve the problem that led you here. You will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.


📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.


Learn to Compare Three Columns in Excel Using Vlookup with These 5 Methods

Here we will be demonstrating how to compare three different columns in excel using VLOOKUP. With step-by-step procedures and proper pictures, you will learn to solve such problems in this article.


method 1

1. Applying VLOOKUP Function

In this section, we will be using a dataset that has the info of students from different states with their first and last names as well as the department they are studying in. However, we will be determining their department from the list comparing the other 3 info. Here we will be using the VLOOKUP function.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, we will create a Helper named column to add all the information together. Therefore I am providing the necessary formula for that.

=B6&C6&D6

how to compare three columns in excel using vlookup using &

  • Now we have a column that has all the data so we can use it to get our desired specific data from the chart. Thus we created two new cells and one of them helped us to specify what we are looking for. Copy the following formula to the other cell for instance our cell has the name Department.

=VLOOKUP(H6,E6:F13,2,0)

  • Press Enter to get the results.

📕 Read More: 2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel


Method 2
  \

2. Utilizing Index-Match Combination

In this section, we will use a combination of multiple functions to compare three different columns in excel using VLOOKUP. For this method, we need a new table to define our criteria. For instance, here we are going to find Peter Jackson from Atlanta studies in which department. Here we will be using the INDEX and MATCH functions.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select a cell and copy down the following formula.

=INDEX(E6:E13,MATCH(1,(B6:B13=B16)*(C6:C13=C16)*(D6:D13=D16),0))

🔨  Formula Breakdown

👉  (B6:B13=B16)*(C6:C13=C16)*(D6:D13=D16)

Determines if all the criteria satisfy. The ( * ) sign states that all these conditions must satisfy together.

👉  MATCH(1,(B6:B13=B16)*(C6:C13=C16)*(D6:D13=D16),0)

MATCH function finds the relative position of multiple values.

👉  INDEX(E6:E13,MATCH(1,(B6:B13=B16)*(C6:C13=C16)*(D6:D13=D16),0))

The INDEX function provides a value in accordance with a cell reference and the column and the row numbers. However here the reference is E6:E13.

👉  Output– Philosophy.

  • Press Enter to get the results.

📕 Read More: 5 Ways to Compare 3 Columns for Matches in Excel


Method 3

3. Combining MATCH with VLOOKUP Function

Here we have a different set of data. We have a price list of products at different times of the year. Let’s say we want to know the price of a product in a specific month. Therefore we will be comparing columns to do that. We will use VLOOKUP and MATCH functions.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, we need a custom row to define our criteria. Then we will be copying down the following formula.

=VLOOKUP(B13,$B$6:$E$10,MATCH(C13,B5:E5,0),0)

🔨  Formula Breakdown

👉  MATCH(C13,B5:E5,0)

MATCH function finds the relative position of multiple values.

👉  VLOOKUP(B13,$B$6:$E$10,MATCH(C13,B5:E5,0),0)

Firstly the LOOKUP value is B13 here followed by the table array $B$6:$E$10 and the column index number is defined by the MATCH function. Lastly 0 says we we want the Exact value

👉  Output– $25.00

how to compare three columns in excel using vlookup Using VLOOKUP and Match fuinction

  • Press Enter to get the result.

📕 Read More: 4 Ways to Compare Three Columns in Excel and Return a Value


Method 4

4. Using XLOOKUP to Get Entry from Three Columns

Here we will be using XLOOKUP function to compare three columns in excel.

⬇️⬇️ STEPS ⬇️⬇️

  • First, make a custom Row to define the criteria. After that, copy the following formula.

=XLOOKUP(1,(B6:B13=B16)*(C6:C13=C16)*(D6:D13=D16),B6:F13)

how to compare three columns in excel using vlookup XLOOKUP function

  • Press Enter to get the results.

📕 Read More: Excel Formula: Compare Two Columns and Return a Value


Method 5

5. Using FILTER Function to Get Entry from Three Columns

Here we will be comparing three columns in excel using the FILTER function.

⬇️⬇️ STEPS ⬇️⬇️

  • First, make a custom Row to define the criteria. After that copy the following formula.

=FILTER(B6:F13,(B6:B13=B16)*(C6:C13=C16)*(E6:E13=E16))

how to compare three columns in excel using vlookup Filter function

  • After that press Enter to get the results.


📄 Important Notes

🖊️  Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

🖊️  Use shortcuts to reduce time.

🖊️  Use Ctrl+Shift+Enter for arrays if you are not using Microsoft 365.

🖊️  While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

🖊️  Be careful while defining criteria to be accurate as the dataset.

🖊️  Use F4 for absolute values.


📝 Takeaways from This Article

📌  Use of INDEX, MATCH, VLOOKUP,  FILTER, and XLOOKUP functions.

📌  How to compare three columns in Excel using VLOOKUP.

📌  How to use multiple functions to create a formula.

📌  Determining multiple answers for given criteria.


Conclusion

Firstly, I hope you were able to use the techniques I demonstrated in this How to Compare Three Columns in Excel Using VLOOKUP lesson. As you can see, there are a lot of options on how to do this. Decide deliberately on the approach that best addresses your circumstance. I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the How to Compare Three Different Columns in Excel Using VLOOKUP Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.


Related Articles

(Visited 85 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo