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

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

**These columns are a sales representativeâ€™s total sales information for a particular product.**

*Sales.*### 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**,*from another table and retrieve the**Region**, and**Product**for matches. To do so,*Sales* - 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**.

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

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

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

**Similar Readings**

**How to Compare 4 Columns in Excel VLOOKUP (Easiest 7 Ways)****Excel Formula to Compare and Return Value from Two Columns****How to Match Multiple Columns in Excel (Easiest 5 ways)****Compare Two Columns for Finding Differences in Excel**

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

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

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

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

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