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.


Compare Three Columns and Return a Value in Excel: 4 Easy Ways

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.

Dataset


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.

Helper Column

VLOOKUP

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

compare three columns in excel and return a value

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.

Ampersand


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.

VLOOKUP compare three columns in excel and return a value

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.

Output compare three columns in excel and return a value


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.

INDEX MATCH compare three columns in excel and return a value

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.

compare three columns in excel and return a value

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.

SUMPRODUCT compare three columns in excel and return a value


Download Practice Workbook

Download this workbook and practice while going through the article.


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.


<< Go Back to Columns | Compare | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

4 Comments
  1. 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 Avatar photo
      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. 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo