4 Ways to Compare Three Columns in Excel and Return a Value

In a dataset, you may need to extract data from a data table by comparing three columns. Excel has a ton of fantastic comparison-supporting features, such as conditional formatting and lookup.
To get the desired outcome, it is also possible to create unique formulas. This article will discuss how to compare three columns in Excel and return a value.


📁 Download Excel File

Download the Excel file we used to create this article so you can practice.


Learn to Compare Three Columns in Excel and Return a Value with These 4 Suitable Approaches

Suppose you have a dataset from a factory, where there are five sections and each section contains two shifts, morning and evening. And you are also given the data on sales from each section. What you have to do is compare three columns of your dataset and then return the desired value. So let’s see how to do that.


Approach

1. Using IF function

Among the logical functions of Excel, the IF function is one of the most widely used. This function allows you to compare three columns in a table. To compare three columns, you have to run logic three times, so we are going to use the IF function three times in our formula.

Here are the steps:

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the cell where you want to see the result; here, we have used cell J6 to see the sales by comparing three columns.
  • Secondly, type the formula below using the IF function.

=IF(B6:B10=G6,IF(C6:C10=H6,IF(D6:D10=I6,E6:E10)))

🔨 Formula Breakdown

IF(B6:B10=G6,IF(C6:C10=H6,IF(D6:D10=I6,E6:E10)))

👉  The  IF function used as IF(D6:D10=I6,E6:E10), where D6:D10 represents the column range of the column that we are using to compare with another column, and so, when any value in this range matches the criteria I6 , it returns the corresponding value in the range E6:E10.

So, IF(B6:B10=G6,IF(C6:C10=H6,IF(D6:D10=I6,E6:E10))) becomes IF(B6:B10=G6,IF(C6:C10=H6,E6:E10)).

👉  After that, in IF(C6:C10=H6,E6:E10), again  the IF function compares the range of the column C6:C10 with the value stored in H6. If they match after comparing, it returns the corresponding value in the range of cells E6:E10, which is the range of sales. So, the function becomes IF(B6:B10=G6,E6:E10).

👉  Then, in this final IF function, again the range  B6:B10 which has the value of sections, is compared with the values that are stored in the cell G6, if any of the values match, it will return the True command, and assign the corresponding value from the range E6:E10.

👉  So, it compared three cell values (G6 to I6) with the three table columns and returned the value $500.

Using IF function to Compare Three Columns in Excel and Return a Value

  • Thirdly, from your keyboard, press the Shift+Ctrl+Enter and you will see the output, which is the value of sales, comparing the three columns before.

  • Finally, use the Fill Handle icon to fill up all the cells in that column.

Applying IF function to Compare Three Columns in Excel and Return a Value

  • Drag it downwards and see the results.

📕 Read More: Excel Formula: Compare Two Columns and Return a Value


Approach

2. Applying VLOOKUP Function

The VLOOKUP function searches for the data you are looking for with criteria and returns an exact or approximate match. Let’s follow the steps below to learn how to use this function.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, you have to join the three columns of data together using &. Here, we have joined the first three columns of the first row by entering the following.

=B6&C6&D6

  • Fill the rest of the column by dragging the Fill Handle icon downwards.

Using VLOOKKUP function -Compare Three Columns in Excel and Return a Value

  • After that, select a cell where you want to enter your formula. We have used the cell.
  • Then, type the formula below.

=VLOOKUP(H6&I6&J6,$E$6:$F$10,2,0)

Here, H6, I6, and J6 are the cell numbers where values are stored for comparison and using the & operator, these values are concatenated.

$E$6:$F$10 represents the range of the table where we have stored the previously concatenated values and the range of the sales we want in return after comparison.

2 is the number of columns or Col_index_num  that we are counting in, and 0 is the Range_lookup  which indicates False.

  • Finally, use the Fill Handle icon to fill up all the cells in that column.

Using VLOOKKUP function -Compare Three Columns in Excel and Return a Value

  • Drag it downwards and see the results.

Applying VLOOKUP function to Compare Three Columns in Excel and Return a Value

📕 Read More: 5 Ways to Compare Three Columns Using Vlookup in Excel


Approach

3. Utilizing SUMPRODUCT Function

The SUMPRODUCT function is used to sum the products of ranges. We will use it here to compare three columns and return one value. Go through the steps below to learn how you can do this.

⬇️⬇️ STEPS ⬇️⬇️

  • To begin with, go to the cell where you want to see the result, here we have used cell J6 to see the sales by comparing three columns.
  • After that, type the formula below using the SUMPRODUCT function.

=SUMPRODUCT(–(B6:B10=G6),–(C6:C10=H6),–(D6:D10=I6),E6:E10)

Here,

B6:B10 is the range of Section and G6 also represents the value of sections that we are going to compare with.

C6:C10 is the range of Morning Shift and H6 also represents the value of one shift that we are going to compare with and see if it matches the criteria.

Similarly, D6:D10 is the range of the Evening Shift and H6 represents the value of the shift that we are going to compare to and see if it matches the criteria.

TRUE or FALSE is indicated by the double negative sign (–).

Using SUMPRODUCT function to Compare Three Columns in Excel and Return a Value

  • Then, use the Fill Handle icon to fill up all the cells in that column.

Applying SUMPRODUCT function to Compare Three Columns in Excel and Return a Value

  • Drag it downwards and see the results.

📕 Read More: 2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel


Approach

4. Combining INDEX and MATCH Functions

Until now, we have used only single functions to get the value by comparing three columns. But now we are going to combine the two functions, the INDEX, and MATCH functions.

Follow the steps below to learn how you can do so.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the cell where you want to see the result; here, we have used cell J6 to see the sales by comparing three columns.
  • Secondly, insert the formula below.

=INDEX($E$6:$E$10,MATCH(1,($B$6:$B$10=G6)*($C$6:$C$10=H6)*($D$6:$D$10=I6),0))

🔨 Formula Breakdown

INDEX($E$6:$E$10,MATCH(1,($B$6:$B$10=G6)*($C$6:$C$10=H6)*($D$6:$D$10=I6),0))

👉  This type of formula is called an array formula, and to insert this formula, one must use Ctrl + Shift + Enter instead of Enter.

👉  In this formula, ($B$6:$B$10=G6)*($C$6:$C$10=H6)*($D$6:$D$10=I6) is to match and compare the columns.

  • In, $B$6:$B$10=G6 , $B$6:$B$10 represents criteria 1, and G6  is for range 1.G6 will be measured against Criteria 1$B$6:$B$10.
  • Similarly, for $C$6:$C$10 = H6 and $D$6:$D$10=I6, criteria 2 and 3 are $C$6:$C$10 and $D$6:$D$10 respectively, and these are compared against the ranges 2 and 3, which are indicated by H6 and I6, respectively.
  • If these are compared against the range 2 and range 3 which are indicated by H6 and
  • If these comparisons match, then it will return True and if they don’t match, it will return False.
  • You have to use * sign to find the value based on more than one criterion, and it converts every True or False into 1 or 0 and produces an array.

👉  Here,  the 1 after the MATCH function is the lookup value. It finds the first 1 in the array and passes it to INDEX, which returns a value in this row from the specified column.

👉  Again in INDEX($E$6:$E$10,MATCH(1,($B$6:$B$10=G6)*($C$6:$C$10=H6)*($D$6:$D$10=I6),0)), $E$6:$E$10, represents the return_range, which  is the range to return a value from the section column.

Using INDEX and MATCH function to Compare Three Columns in Excel and Return a Value

  • Finally, use the Fill Handle icon to fill up all the cells in that column.

Applying INDEX and MATCH function to Compare Three Columns in Excel and Return a Value

  • Drag it downwards and see the results.

Utilizing INDEX and MATCH function to Compare Three Columns in Excel and Return a Value

📕 Read More: 5 Ways to Compare 3 Columns for Matches in Excel


📝 Takeaway from This Article

📌  You have learned how to use functions to compare three columns and pull one value from the table.

📌  We have broken every formula that we employed so that you can find it easy to use.


Conclusion

We have shown four possible approaches to compare three columns in Excel and return a value.This method may rescue you from unwanted hassles and save you time when you are dealing with vast amounts of data. Please leave a comment if you have any suggestions or questions. Don’t forget to visit our Excelden page to enhance your Excel-related knowledge.


Related Articles

(Visited 49 times, 1 visits today)
Lamisa Musharrat

Lamisa Musharrat

Hey there! I am Lamisa Musharrat, a Marine Engineer and a Content Developer. I love to help people by writing articles for them. Hope you will enjoy my blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo