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