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

- Now press
**Enter**and**copy down the formula**along the column.

- 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

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

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

- 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

**4 Ways to Compare Two Columns in Excel for Missing Values****How to Count Matches in Two Columns in Excel**