Excel Formula to Compare and Return Value from Two Columns

Last updated: September 17, 2023
Get FREE Advanced Excel Exercises with Solutions!

Excel is one of the significant applications used in both computers and mobile. We cannot think of a modern corporate house that is not using Excel. Working with Excel means lots of data, columns, and rows. In this article, we discuss the 4 Examples formula to compare two columns and return a value in Excel with proper illustrations.

We will apply 4 different methods to compare two columns and return a value. We may need to use different data sets for different methods. Initially, we are taking data of some project managers’ names working on two projects.

Data set to Formula to Compare Two Columns and Return a Value in Excel

1. Combining Formula with Excel IF and EXACT Functions to Compare Two Columns and Return Value

The IF function allows us to make logical comparisons between a value and our desire. This IF statement can have two results. The first result is if our comparison is True, the second is if our comparison is False.

Syntax:

IF(logical_test, value_if_true, [value_if_false])

Argument:

logical_test – The desired condition we want to test.

value_if_true – The value we want to return if the result of logical_test is TRUE.

value_if_false – The value will return if the result of logical_test is FALSE.


1.1 Case-insensitive Approach

This approach is not case-sensitive. That means Jeo and jeo are considered the same.

Step 1:

  • Go to Cell D5.
  • Write the following formula:
=IF(B5=C5,"Match","No Match")

IF-EXACT Functions to Compare Two Columns and Return a Value

Step 2:

  • Then, press Enter.

Step 3:

  • Pull the Fill Handle icon.

IF-EXACT Functions to Compare Two Columns and Return a Value

Now, in the status column, we see that Match for where both columns are the same otherwise No Match.


1.2 Case-sensitive Approach

The EXACT function compares two text strings and returns TRUE if they are the same, otherwise shows FALSE. EXACT is case-sensitive but ignores formatting differences.

Syntax:

EXACT(text1, text2)

Arguments:

text1 – It is the first text string.

text2 – It is the second text string.

Here, we will use the Exact Function. Which gives a match result with case sensitivity.

Step 1:

  • Go to Cell D5 like previous.
  • Modify the IF function and EXACT function with this. So, the formula is:
=IF(EXACT(B5,C5),"Match","No Match")

IF-EXACT Functions to Compare Two Columns and Return a Value

Step 2:

  • Press the Enter button.

Step 3:

  • Drag the Fill Handle icon.

Here, we see that No Match is showing in the 10th row, because of case sensitivity.

Read More: How to Compare Two Columns or Lists in Excel (4 Suitable Ways)


2. Merging Excel IF, ISNA and MATCH Functions to Return Mismatched Items from 2nd Column

The MATCH function is used to find any matched object. If it finds any match then returns the position from the text or text series.

Syntax:

MATCH(lookup_value, lookup_array, [match_type])

Arguments:

lookup_value This object will be searched in the lookup_array. It may be any text, numeric value, etc.

lookup_array This is our range where we will search for lookup_value.

match_type It is optional. It defines the type of matching.

The ISNA function is a version of the IS function.

Syntax:

ISNA(value)

Argument:

value Value refers to the #N/A (value not available) error value.

Step 1:

  • First, go to Cell D5.
  • Then, write down the formula. The formula is:
=IF(ISNA(MATCH(C5,$B$5:$B$12,0)),C5,"")

IF+ISNA+MATCH Functions to Return the Mismatched from 2nd Column

Step 2:

  • Now, press Enter.

Step 3:

IF+ISNA+MATCH Functions to Return the Mismatched from 2nd Column

In the status box, we see those names that are present only on Project 2 not in Project 1.

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


Similar Readings


3. Inserting Formula with VLOOKUP Function to Compare and Return Value from Two Columns

The VLOOKUP function is used when we need to find things in a table or a range regarding rows. In VLOOKUP we need to arrange data in a way so that it can check data towards the left. And provides a result after checking.

Syntax:

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

Argument:

lookup_value This value will be checked. It stays in the initial column of the data range.

table_array This is the range from where we will search the lookup_value.

col_index_num This number defines from which column we need the return value.

range_lookup This is optional. This argument defines the type of matching. Those are exact matching or approximate matching.

In this section, we will use the VLOOKUP function. Before that, we need to modify our data. After modification data will look like this.

Step 1:

  • In the data set, we add two boxes for Project ID and Manager. We will input the Project ID and get Manager’s name instantly.

Step 2:

  • We put Project ID A-003.

Formula with the VLOOKUP Function to Compare Two Columns

Step 3:

  • Go to Cell F6.
  • Write the VLOOKUP So, the formula is:
=VLOOKUP(E6,B5:C12,2,TRUE)

Formula with the VLOOKUP Function to Compare Two Columns

Step 4:

  • Then press Enter.

We get the Manager’s name by inputting the Project ID.

Step 5:

We change the Project ID and press Enter to see the changes.

Formula with the VLOOKUP Function to Compare Two Columns

Read More: VLOOKUP Formula to Compare Two Columns in Different Excel Sheets!


4. Joining INDEX and MATCH Functions to Compare Two Columns and Return Value

The IFERROR function is used to check errors. It can easily find any error after evaluating a value. If no error is found then give a proper value of the function.

Syntax:

IFERROR(value, value_if_error)

Arguments:

value The given weight will be used to check to find the error.

value_if_error – This value will be shown if any error is found.

The INDEX function is used in two ways. If we want to return the value of a specified cell or array of cells, see Array form. Or see the reference form If we want to return a reference to specified cells.

Syntax:

INDEX(array, row_num, [column_num])

Arguments:

array It is the range of cells or an array constant.

row_num  It is required unless column_num is present. It selects the row in an array from which to return a value. If row_num is omitted, column_num is required.

column_num It elects the column in the array from which to return a value. If column_num is omitted, row_num is required.

Step 1:

  • We took A-007 as the Project ID in Cell E6.

Step 2:

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

INDEX-MATCH Functions to Compare Two Columns and Return a Value

Step 3:

  • Then press Enter.

We get the manager’s name according to the project id. If we give any input on the Project id box that is not present on our data set see what happens.

Step 4:

  • We give an input A-010 and press Enter.

INDEX-MATCH Functions to Compare Two Columns and Return a Value

The result is showing #N/A. To avoid those values we will insert the IFERROR function.

Step 5:

  • Add the IFERROR function with the existing formula. So, the formula will be:
=IFERROR(INDEX(C5:C11,MATCH(E6,B5:B12,0)),"")

INDEX-MATCH Functions to Compare Two Columns and Return a Value

Step 6:

  • Then, press Enter.

Now, due to using the IFERROR function if the reference is not found on the data the result will be blank.

Formula Breakdown:

  • MATCH(E6,B5:B12,0)

This function searches for a match of Cell E6 in the range B5 to B12.

Output: 7

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

This function searches the output of the MATCH function on the range C5 to C11.

Output: Josef

  • IFERROR(INDEX(C5:C11,MATCH(E6,B5:B12,0)),””)

This will return the value of INDEX if the value is valid, otherwise, the cell will show blank.

Output: Josef


5. Comparing Two Columns and Return Value from Third Column

In this section, we will compare two columns and get results from the third column. We modify the data set first.

INDEX-MATCH Functions to Compare Two Columns and Return a Value

Step 1:

  • We will use Project ID and Manager as references to compare and get the output from Salary.

Step 2:

  • Give input on the reference boxes.

INDEX-MATCH Functions to Compare Two Columns and Return a Value

Step 3:

  • Now, write the formula in Cell C16. The formula is:
=INDEX(D5:D12,MATCH(C14&C15,B5:B12&C5:C12,0))

INDEX-MATCH Functions to Compare Two Columns and Return a Value

Step 4:

  • Then, press Ctrl+Shift+Enter as it is an array function.

Now, we see that the result is from the third column.

Read More: Match Two Columns in Excel and Return a Third (3 Ways)


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion 

In this article, we showed an Excel formula to compare two columns and return a value. I hope this will satisfy your needs. Please have a look at our website and give your suggestions in the comment box.


Related Articles

Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo