Lamisa Musharrat
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.
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.
- 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.
- Drag it downwards and see the results.
Read More: Excel Formula: Compare Two Columns and Return a Value
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.
- 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.
- Drag it downwards and see the results.
Read More: 5 Ways to Compare Three Columns Using Vlookup in Excel
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 (–).
- Then, use the Fill Handle icon to fill up all the cells in that column.
- Drag it downwards and see the results.
Read More: 2 Ways to Compare 4 Columns Using VLOOKUP Function in Excel
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.
- Finally, use the Fill Handle icon to fill up all the cells in that column.
- Drag it downwards and see the results.
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
- 4 Ways to Compare Two Columns in Excel for Missing Values
- How to Count Matches in Two Columns in Excel
