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.

**Table of Contents**Expand

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

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

- Then, use
**Fill Handle**to**AutoFill**up to**E14**.

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

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

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

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

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

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

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

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

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

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

Hometab >> chooseSort & Filterfrom #diting group >> pickFilter.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

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