Excel Formula to Compare Two Columns and Return a Value

You can compare data in a column and match the content to a different column. Different Excel formulas 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 the Autofill feature or drag the fill handle down to the cells you want to copy the formula.

Option two:

1. Enter the following formula on cell E2


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 a 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 columns A and D and if a match is found, return the corresponding name in column B. #N/A is returned if no match is 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 your excel workbook, put your cursor on the cell where the value is to be returned.

2. Click on Kutools, select the 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

%d bloggers like this: