# How to Compare Three Columns and Return a Value in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

1. Reply 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

• Reply Fahim Shahriyar Dipto Dec 28, 2022 at 11:35 PM

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

2. Reply 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

• Reply Shamima Sultana Apr 27, 2023 at 8:50 AM

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 Advanced Excel Exercises with Solutions PDF  