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

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


 

 

 

 

 

 

 

 

Option 5: Combining IF And EXACT Functions

The IF function in this method allows you to compare a value and your desire logically. Hence, the IF formula can give two results where the first result is if your comparison is True and the second result is if your comparison is False. The syntax of the formula is;

IF (logical_test, value_if_true, [value_if_false]), where;

logical-test is your desired condition you want to test.

value-if-true is the value you want to return if the logical test is TRUE.

value-if-false is the value you want to return if the logical test is FALSE.

To use the combination of IF and EXACT functions to compare two columns, we can, therefore, use two approaches as discussed below.

Case-Insensitive Approach

You can use this approach if you consider data in two columns the same. In this case, you can follow these steps;

1. Write this formula in cell D5, or in the third column next to the data you want to compare.

=IF (B5=C5,"Match","No Match")

2. Press Enter and the formula will display in the formula cell.

3. Use the Fill Handle (+) icon to drag the formula to other cells.

4. The formula will give a Match result for columns with similar data and No Match for different data.

Case-Sensitive Approach

The EXACT function in this approach compares two data and returns TRUE values if they are the same and FALSE values if they are different. It also ignores formatting differences, hence a reason why it is case-sensitive. Its syntax is:

EXACT (text1, text2), where;

text1 is the first text string.

text2 is the second text string.

Steps:

1. Go to the third column next to the data you want to compare, like in the first approach, and write this modified formula:

=IF (EXACT (B5, C5),"Match”, “No Match")

2. Press Enter and the formula will show up in the formula cell.

3. Use the Fill Handle (+) icon to drag the formula to other cells.

4. The approach can give a No Match result even for matching data with slight formatting differences.

Option 6: Using VLOOKUP Function

You can use the VLOOKUP function if you want to find data in a table or a range of rows. In this case, you need to arrange data so that you can check it from the right and have a return value after comparing. The syntax of the function is:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]), where;

lookup-value is the value you need to check and stays in the initial column of the data range.

table-array is the range from which you will search the lookup value.

col-index-num is the number that defines the column from which you need to return the value to appear.

range-lookup (optional) defines the matching type, such as exact and approximate matching.

To use the VLOOKUP function, you may need to modify your data before comparing. After modification, you can proceed with the following steps:

1. Create two more cells to store return values after comparing, let's say cells E6 and F6.

2. Write one data in cell E6, such as Project ID.

3. Write the following VLOOUP formula in cell F6;

=VLOOKUP (E6, B5:C12,2, TRUE)

4. Press Enter and you will return the value in cell F6.

5. You can now change the Project ID in cell E6 and press Enter to generate different results in cell F6.

Option 7: Combining INDEX, MATCH, And IFERROR Functions

To explain this method, you can still use the same data and approach in Method 3. That means you can combine INDEX and MATCH functions to compare and find the return value in cell F6 whenever you change the data in cell E6. In this case, you need to compare a range of data or table array by writing the following combination formula in cell F6;

=INDEX (array, row_num, [column_num])), where;

array is the range of cell or an array constant, for example, $C5:C11.

row-num selects the array of rows from which you want to return the value.

column-num is the array of columns from which you want to return the value.

Therefore, the general formula for the two functions can change to

=INDEX (C5:C11, MATCH (E6, B5:B12,0))

However, if you change the data in cell E6 and put anything not present in your dataset, it can give a NA error. Therefore, to avoid such errors, you can introduce the IFERROR function to the combination formula, which changes it to:

=IFERROR (INDEX (C5:C11, MATCH (E6, B5:B12,0)),””), which will give an empty cell if the value is not found, unlike in the first case that returns a NA error.

The combination formula also requires you to modify the dataset and include another reference box. For example, you can create other cells to store Project ID, Manager’s Name, and Salary. After inputting the project’s ID and corresponding manager’s name in their respective cells, (say cell C14 for project ID and C15 for manager), you can proceed with the following steps:

1. Write this formula in cell C16

=INDEX (D5:D12, MATCH (C14&C15, B5:B12&C5:C12,0))

2. Press the Ctrl + Shift + Enter keys altogether since this is an array function.

3. The formula will give the salary of the manager in cell C16. You can, therefore, change the Project ID and Manager’s Name and press Enter to get their salaries.