Shamima Sultana
We may need to extract data by comparing three columns or to get an insight into data we may need to compare three columns. In this article, I will explain how you can compare three columns in Excel and return a value.
Download Practice Workbook
Download this workbook and practice while going through the article.
4 Easy Ways to Compare Three Columns and Return a Value in Excel
To make it more visible I will use a sales information dataset from different regions. There are 4 columns in the dataset: Sales Rep, Region, Product, and, Sales. These columns are a sales representative’s total sales information for a particular product.
1. Use VLOOKUP Function to Compare Three Columns
You can use the VLOOKUP function to compare three columns in Excel along with returning a value. Here are the steps.
Steps:
- First of all, create a Helper Column.
- Then, go to E5 and write down the following formula
=B5&C5&D5- Afterward, press ENTER to get the output.
- Then, use Fill Handle to AutoFill up to E14.
- Next, I will compare the three columns Sales Rep, Region, and Product from another table and retrieve the Sales for matches. To do so,
- Go to K5 and write down the following formula
=VLOOKUP(H5&I5&J5,$E$5:$F$14,2,0)- Then, press ENTER to get the output.
Formula Explanation
- Here, the lookup_value is H5&I5&J5.
- The table_array is $E$5:$F$14. Excel will look for H5&I5&J5 in this array.
- The col_index is 2. That means, Excel will return the corresponding sales.
- Then, use Fill Handle to AutoFill up to K14.
Read more: How to Compare Three Columns in Excel Using VLOOKUP
2. Combine INDEX and MATCH Functions to Compare Three Columns and Return a Value in Excel
We can use the INDEX function with the MATCH function to compare three columns. Look at the steps.
Steps:
- Go to J17 and write down the following formula
=INDEX($E$5:$E$14,MATCH(1,($B$5:$B$14=G5)*($C$5:$C$14=H5)*($D$5:$D$14=I5),0))- Press ENTER to get the output.
Formula Breakdown
- ($B$5:$B$14=G5)*($C$5:$C$14=H5)*($D$5:$D$14=I5) → This is the lookup_array for the MATCH function.
- Output: {1;0;0;0;0;0;0;0;0;0}
- MATCH(1,($B$5:$B$14=G5)*($C$5:$C$14=H5)*($D$5:$D$14=I5),0) → This is the row_num for the INDEX function.
- Output: 1
- INDEX($E$5:$E$14,MATCH(1,($B$5:$B$14=G5)*($C$5:$C$14=H5)*($D$5:$D$14=I5),0)) → This becomes,
- INDEX($E$5:$E$14,1)
- Output: $16,800
- Then, use Fill Handle to AutoFill the formula up to J14.
Read More: How to Compare Two Columns in Excel for Match (8 ways)
Similar Readings
- How to Compare 4 Columns in Excel VLOOKUP (Easiest 7 Ways)
- Excel Formula to Compare and Return Value from Two Columns
- How to Match Multiple Columns in Excel (Easiest 5 ways)
- Compare Two Columns for Finding Differences in Excel
3. Use IF Function to Compare Three Columns in Excel
Now, I will show another method that requires the IF function. Let’s learn the method.
Steps:
- First of all, go to J5 and write down the following formula.
=IF(B5:B14=G5:G14,IF(C5:C14=H5:H14,IF(D5:D14=I5:I14,E5:E14)))- Then, to get the output, press CTRL + SHIFT + ENTER since this is an array formula. You will get all the salaries at once.
Formula Explanation
- This is a nested IF formula.
- B5:B14=G5:G14 is the logic test for the 1st IF function.
- Output: {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
- Since the output is TRUE, the result will be IF(C5:C14=H5:H14,IF(D5:D14=I5:I14,E5:E14))
- The rest IF functions work similarly to the 1st one.
- Output: {16800;18700;45780;9040;10100;24500;50000;5500;20000;2000}
Notice the curly bracket {} in the formula bar. It indicates an array formula.
Read More: How to Compare Two Columns and Return Common Values in Excel
4. Apply SUMPRODUCT Function to Compare Three Columns and Return a Value in Excel
Now, I will explain how you can compare three columns and return a value by using the SUMPRODUCT function. The steps are as follows.
Steps:
- Go to J5 and write down the following formula
=SUMPRODUCT(--(B5:B14=G5),--(C5:C14=H5),--(D5:D14=I5),E5:E14)- Then, press ENTER to get the output.
Formula Explanation
- There are four arrays in the formula.
- The output for the 1st three arrays are the same, {1;0;0;0;0;0;0;0;0;0}
- Hence the sum products of these arrays are the corresponding Sales.
- Then, use Fill Handle to AutoFill the formula up to J14.
Read More: Excel formula to compare two columns and return a value (5 examples)
Conclusion
In this article, I’ve explained 4 methods to compare three columns in Excel and return a value. These different approaches will help you compare multiple columns. Feel free to comment down below to give any kind of suggestions, ideas, and feedback.
Hello, I have 3 columns. employee ID, date and dollars. Not all dates for each employee have dollar. I want to return dates that have dollars for each employee. How do I proceed.
Thank you
Hello Jim,
Thanks for commenting. If I am not wrong, you want to sort dates that don’t have the dollar like the dataset below.
Select the entire data range then navigate the Home tab >> choose Sort & Filter from #diting group >> pick Filter.
Choose the Filter dropdown and uncheck the blanks from the column.
Finally, the dates are sorted with the dollar.
Regards
Fahim Shahriyar Dipto
Excel & VBA Content Developer
Hello, i want to match 3 columns Customer name, ID name and Real name resp. and if both name not matched with ID name then , they should be highlighted with colour.
ex.
jenith jeneth jenath riq
Hello Chandan,
Thanks for commenting. If I’m not wrong, you want to match the dataset like the one below.
Select the entire dataset without the heading then go to conditional formatting >> New Rule
Then select “Use a formula to determine which cells to format.”
In the formula bar, enter the following formula:
=AND(A3<>B3,C3<>B3)
Press OK
Here, “A3” represents the cell containing the customer name, “B3” represents the cell containing the ID name, and “C3” represents the cell containing the real name. You can adjust the cell references based on your data.
Choose the formatting option that you want to use for the highlighted rows. For example, we took light green color here for highlighting.
The final outcome will look like the image below.
I hope this answer will help you to identify matched names. Please let us know if you have any other queries. Also, you can post your Excel-related problem in ExcelDemy Forum with images or Excel workbooks.
Regards
Mizbahul Abedin | ExcelDemy Team