## Method 1 Compare Two Columns and Return a Value from the Second Column with the VLOOKUP Formula

In the following spreadsheet, we have a list of Projects and their Managers. In cell **D2**, the **Project coordinator** might input a **Project** name and want to see who the **Manager** of the **Project** is.

- Use this formula in cell
**E2**:**=IFERROR(VLOOKUP(D2,project_manager,2,FALSE), “Not Assigned”)**

### How does this formula work?

To understand this formula, you have to know the syntax of **IFERROR** and **VLOOKUP** Excel functions.

Here’s how the formulas join together to get a result.

Here’s a full review of what each part does.

**Read More: ****How to Match Two Columns and Return a Third in Excel**

## Method 2 – Compare Two Columns and Return a Value (using INDEX and MATCH functions)

- Use the following formula for cell
**E2**:**=IFERROR(INDEX(B2:B16, MATCH(D2,A2:A16,0)), “”)**

If the formula doesn’t find a match, it won’t return a value since the **IFERROR** function’s **value_if_error** argument is blank.

### How does the formula work?

Check out the following image.

Here’s how the formula works in steps:

**The MATCH**function returns the relative position of**lookup_value****D2**. Say, the value is**DD**. It will return 4 as**DD**is in 4^{th}position in the**lookup_array****$A$2:$A$16**. If no matching was found, the MATCH function would return an error value.**The INDEX**function searches for the 4^{th}value in the array**$B$2:$B$16**. And it finds value,**John**. So, for this example, the INDEX function will return the value,**John**.**The IFERROR**function finds a valid value for its**value**So, it will return the value. If it would find an error value, then it would return a blank.

**Read More:** **How to Count Matches in Two Columns in Excel (5 Easy Ways)**

## Method 3 – Two Columns Lookup

Here’s a dataset of employees and their salaries. We’ll find a salary for an employee with a given first and last name.

You cannot perform two columns lookup with regular Excel formulas. You have to use an **Array formula**.

- Input this formula in cell
**F4**:**{=INDEX(C2:C11,MATCH(F2&F3, A2:A11&B2:B11,0))}**

- Press
**Ctrl + Shift + Enter**on your keyboard. You will get the formula as an array formula.

### How does this array formula work?

At first, let’s understand the **Match** function part.

You can imagine this array formula as a series of the following formulas:

**MATCH(F2&F3, A2&B2,0)****MATCH(F2&F3, A3&B3,0)****MATCH(F2&F3, A4&B4,0)**- … … …
- … … …
**MATCH(F2&F3, A11&B11,0)**

This series will be stored in Excel memory as `<code>`

**MATCH (JamesSmith, {“MarissaMayer”; “MarissaAhmed”; “MarissaKawser”; “ArissaAhmed”; “ArissaKawser”; “JamesClark”; “JamesSmith”; “JohnWalker”; “JohnReed”; “JohnLopez”}, 0)**

From the above MATCH function, what will be returned? **MATCH** function will return 7 as **JamesSmith** is found at position 7 of the array.

The rest is simple. In the array of **C2:C11**, the 7^{th} position is value **210745**. So, the overall function returns **210745** in cell **F4**.

**Read More:** **How to Compare Text Between Two Cells in Excel (10 Methods)**

**Similar Readings**

**How to Compare Two Columns in Excel for Missing Values (4 ways)****Match Two Columns and Output a Third in Excel (3 Quick Methods)****Excel Formula to Compare and Return Value from Two Columns****Excel Macro to Compare Two Columns (4 Easy Ways)****How to Match Multiple Columns in Excel (Easiest 5 ways)**

## Method 4 – Compare Two columns and List Differences in the Third Column

Here are two lists that we need to compare and show the values of **List 2** under a new column but without the values that are also in **List 1.**

- Use this formula in cell
**C2**:**=IF(ISNA(MATCH(B2, $A$2:$A$8,0)),B2, “”)** - Drag the formula down to copy it for the other cells.
- We get the following results for the sample.

### How does this formula work?

Let’s break down the formula into pieces:

**The Match**function will search for cell value**B2**in the range**$A$2:$A$8**. If it finds a match, it will return the position of the value, otherwise, it will return the**#N/A.**Value**600**of cell**B2**is not found anywhere on the list. So, the**Match**function will return the**#N/A**error.**The ISNA**function returns**TRUE**if it finds the**#N/A**error, otherwise, it will return a**FALSE**In this case, ISNA will return TRUE value as Match function returns #N/A error.- When the
**ISNA**function returns a**TRUE**value,**IF**function’s**value_if_true**argument will be returned and it is**B2**, the value of**B2**is**600**. So, this formula will return 600 as a value.

**Read More:** **How to Compare Two Lists and Return Differences in Excel**

## Method 5 – Compare Two Columns Row by Row

You might also want to compare two columns row by row like the following image.

- Use the following formula in cell
**C2**:**=IF(A2=B2, “Matched”, “Not Matched”)** - AutoFill to the other cells in the result column.

This is a straightforward Excel IF function. If the cells **A2** and **B2** are the same, **“Matched”** value will show in cell **C2** and if the cells **A2** and **B2** are not the same, then the **“Not Matched”** value will show as the output.

This comparison is case-insensitive. **“Milk”** and **“milk”** are treated as the same in this comparison.

- We can also use the
**EXACT**function to find the exactly matched values. Change the formula to the following:`=IF(EXACT(A2,B2), "Matched", "Not Matched")`

You see now “**Milk”** and “**milk”** are treated differently. They are not the same.

**Read More: ****How to Compare Text in Two Columns in Excel**

## Download the Working File

## Further Readings

**How to Compare Two Columns or Lists in Excel (4 Suitable Ways)****VLOOKUP Formula to Compare Two Columns in Different Excel Sheets****How to Compare Two Columns for Finding Differences in Excel****Compare Two Columns in Excel and Highlight the Greater Value (4 Ways)****How to Compare Multiple Columns Using VLOOKUP in Excel (5 Methods)**

Thank you very much. First of all, sorry for my bad english, than i want to congrats you for this lection and you have made in me one of the fan of your blog.

Thanks for your feedback. It means a lot to us. Keep following our website for more useful articles.

Very clearly explained. This was a great help!

Thanks for the feedback.

Thanks a lot, I liked your lesson, and liked your way of explaining (how does this formula work?) I like it so much. Thanks again.

You are welcome, Ahmad 🙂

Thank you for this comprehensive and useful tutorial 🙂

You are welcome, Surya 🙂

Thank you! Thank you! Thank you! Your formula: “3) Two Columns Lookup” solved a very complex problem for me. It also has automated a process that has been taking me quite a long time to do manually! Your examples made it very clear as to what I needed to do!

Nice, Roger. I am glad to know that the formula helped you 🙂