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.

**Contents**hide

## 📁 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 I**6**) 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**

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

- 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**

**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 (–).

** **

- 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**

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

- 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**