# 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.

## 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

• 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.

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.

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

• Press Enter to get the result.

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)

• Press Enter to get the results.

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))

• 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.

`📌`  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.