Excel Formula to Compare Two Columns and Return a Value

Excel Formula to Compare Two Columns and Return a Value

  • Post author:
  • Post category:Formula

You can compare data in a column and match the content to a different column. Different Excel formula can be used to compare two columns, find the matches in the column and return a value.

In this article, I will show you different methods you can use to compare data from different columns.

 

Option one

1. Create the table below


 

 

 

 

 

 

 

 

 

 

 

2. Go to cell E2 and enter the formula =IF(ISNUMBER(MATCH(D2,$A$2:$A$20,0)),INDEX(Sheet5!$B$2:$B$20,MATCH(Sheet5!D2,Sheet5!$A$2:$A$20,0)),0)


3. Press ENTER key to get the matching content on the E2.


4. Copy the formula to the rest of the cells using Autofill feature or drag the fill handle down to cells you want to copy the formula.


Option two:

1. Enter the following formula on cell E2

=IF(ISNA(MATCH(D2,$A$2:$A$20,0)),””,VLOOKUP(D2,$A$2:$B$20,2,FALSE))


2. Press ENTER key


3. Autofill the formula to the rest of the cells


 

In the above formulas, cell D2 is the criteria cell that returns value based on column cells A2:A20 which is to be compared with and return the matching value from data range A2:B20.

 

Option three:

You can pull the matching content from one column to another using the Index and match formulas only.

Using the same example, compare the projects in column A and D and if a match is found, return the corresponding name in column B. #N/A is returned if no match found.

1. Enter the formula below

=INDEX ($B$2:$B$20, MATCH(D2,$A$2:$A$20,0))

2. Press ENTER key.


3. Use the fill handle to drag the formula to the rest of the cells you want to match.


Option 4: Kutools Add-in

You can download a free trial Kutools add-in and use it to compare two columns and return the value in a third column.

1. Once you install the add-in to you excel workbook, put your cursor on the cell where the value is to be returned.

2. Click on Kutools, select formulas tab then click Look for a value in the list.


3. A formula helper window opens which allows you to enter the table array, Lookup value and the corresponding column with the return values.


4. Enter the table references and click OK.


5. Autofill the formula to the rest of the cells