Compare Three Columns in Excel and Return a Value(4 Ways)

Using INDEX and MATCH

It may need to extract data by comparing three columns or to get an insight of data we may need to compare three columns. In this article, I’m going to explain how you can compare three columns in Excel and return a value.
To make it more visible I’m going to use a dataset of sales information of the different regions. There are 4 columns in the dataset which are Sales Rep, Region, Product, and, Sales. These columns are the total sales information for a particular product by a sales representative.

Sample Datasheet

Download to Practice

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

1. Using VLOOKUP

You can use the VLOOKUP function to compare three columns in Excel along with returning a value.

I have added a new column name Helper. Here I merged the values of Sales Rep, Region, and Product columns. I will use this column to use the VLOOKUP function to extract a value comparing three columns.

merged columns

For example, I’m going to compare the three columns Sales Rep, Region, Product from another table and retrieve the Sales for matches.

First, select the cell where you want to place your compared value.
Then, type the formula in either the formula bar or in the selected cell.
Here, I selected the J4 cell.
Type the Formula

=VLOOKUP(H4&I4&J4,$E$4:$F$13,2,0)

Using VLOOKUP function

Now press ENTER.
It will extract the Sales value comparing Sales Rep, Region, and Product.

Using VLOOKUP function

Now you also can use the Fill Handle to Autofit formula for the rest of the cells.

Using VLOOKUP function

2. Using INDEX and MATCH

We can use the INDEX function with the MATCH function to compare three columns.

To use these functions first, select the cell where you want to keep your resultant value.
Type the formula either in the formula bar or in the selected cell.
The Formula is

=INDEX($E$4:$E$13,MATCH(1,($B$4:$B$13=G4)*($C$4:$C$13=H4)*($D$4:$D$13=I4),0))

Using INDEX and MATCH function

Finally, press ENTER.
It will compare the selected cell and will give the required value.

Using INDEX and MATCH

You also can use Fill Handle to Autofit the formula for the rest of the cells.

Using INDEX and MATCH

3. Using IF

To use the IF function first select the cell you want to place your resultant value.

Type the formula.
The formula is

=IF(B4:B13=G4,IF(C4:C13=H4,IF(D4:D13=I4,E4:E13)))

Using IF function

Here, press SHIFT + CTRL + ENTER.
It will show the resultant value by comparing it with three selected columns.

Using IF function

Now you also can use the Fill Handle to Autofit formula for the rest of the cells.

Using IF function

4. Using SUMPRODUCT

To compare three columns and return a value you can use the SUMPRODUCT function.

To use this function first select the place where you want to put your resultant value.
Here, I selected the J4 cell.
The formula is

=SUMPRODUCT(--(B4:B13=G4),--(C4:C13=H4),--(D4:D13=I4),E4:E13)

Using SUMPRODUCT

After typing the formula press ENTER.
It will give you the value you want to get by comparing three columns.

Using SUMPRODUCT function

By using the Fill Handle you can Autofit formula for the rest of the cells.

Using SUMPRODUCT function

Note: The SUMPRODUCT function only works for numeric values.

Practice Section

I have given a sheet to practice these functions in the workbook.

Practice Datasheet

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 to compare multiple columns. Feel free to comment down below to give any kind of suggestions, ideas, and feedback.


Further Content:

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo