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.


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.

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

  • Then, use Fill Handle to AutoFill up to E14.

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

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.

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

Read More: How to Compare Two Columns in Excel for Match (8 ways)

Similar Readings


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.

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.

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

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.

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

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