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