Sometimes while working with multiple columns you often need to match your two columns to return the third value. In this article, we will see how to match two columns in Excel and return a third.

**Table of Contents**hide

## Match Two Columns and Return a Third in Excel : 3 Easy Methods

Here we will be comparing two columns where there exist some same values. If the two values get matched then it will return third column values where the values will be corresponding results of the 1st column.

Let’s look into the below table where we have some product IDs along with their corresponding prices. We create another column with the heading Product ID-2. Here we will be comparing the columns Product ID and Product ID-2 to return the value from the Price column in the Price-2 column.

### 1. Use of VLOOKUP Function to Match Two Columns and Return a Third in Excel

In the first method, I will show you the use of **the VLOOKUP function**. Let’s do it step by step.

**Steps:**

- Go to
**F5**and write down the following formula

`=VLOOKUP(E5,$B$5:$C$15,2,FALSE)`

**Formula Explanation:**

- Here, the
**lookup value**is**E5**. - The
**array**is**B5:C15**. - The
**column index number**is**2**. So Excel will return the corresponding price for**E5**. (because the price is in the 2nd column of the array)

- Then, press
**ENTER**to get the output.

- After that, use the
**Fill Handle**to**AutoFill**up to**F9**.

**Read More:** How to Compare Two Columns in Excel for Match

### 2. Combination of INDEX-MATCH Functions to Match Two Columns and Return a Third in Excel

The next method is an important one. Here, I will use a combination of the **INDEX** and **MATCH **Functions. Let’s see the steps.

**Steps:**

- Go to
**F5**and write down the following formula

`=INDEX($C$5:$C$15,MATCH(E5,$B$5:$B$15))`

**Formula Breakdown:**

**MATCH(E5,$B$5:$B$15)**→ Excel will return the relative position**1002**in the array**B5:B15**.**Output: {2}**

**INDEX($C$5:$C$15,MATCH(E5,$B$5:$B$15))**→ This becomes**INDEX($C$5:$C$15,2)****Output: {1029}**

- Now, press
**ENTER**to get the output.

- Finally, use the
**Fill Handle**to**AutoFill**up to**F9**.

**Read More:** How to Compare 3 Columns for Matches in Excel

### 3. Combination of IF, INDEX, and MATCH Functions to Match Two Columns and Return a Third in Excel

Now, I will show another method. For this method, I have changed the dataset a little bit.

This time, I will match both the **Product ID** and **Category** and get the **price**. A combination of **the IF**, **INDEX,** and **MATCH** functions will work here.

**Steps:**

- Go to
**G7**and write down the following formula

`=INDEX(D5:D15,MATCH(G5,IF(C5:C15=G6,B5:B15),0))`

**Formula Breakdown:**

**C5:C15=G6**→ This is the logical test for the**IF**The condition is an array condition.**Output:****TRUE**is for Category C, and**FALSE**is for other categories.**{FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}**

**B5:B15**→ This is the value if the test is**TRUE**.**MATCH(G5,IF(C5:C15=G6,B5:B15),0))**→**G5**is the lookup value and the lookup array is**IF(C5:C15=G6,B5:B15)**, that means Excel will look for**PID-1001**from**{FALSE;FALSE;”PID-1005″;”PID-1001″;FALSE;FALSE;”PID-1009″;FALSE;FALSE;FALSE;FALSE}**and get you the relative position.**Output: {4}**

**INDEX(D5:D15,MATCH(G5,IF(C5:C15=G6,B5:B15),0))**→ This becomes**INDEX(D5:D15,4)****Output: {2186}**

- Then, press
**CTRL+SHIFT+ENTER**to get the output. This is because it’s an array formula. You will see a pair of 2nd brackets appear in the formula which contains the formula inside it.

## Things to Remember

- The absolute reference is for locking a range.
**CTRL+SHIFT+ENTER**is for array formulas.

**Download Practice Workbook**

Download this workbook and practice while going through this article.

## Conclusion

A comparison between columns to find matches and result in a value from a different column is a common practice in Excel. Knowing the solutions to this kind of problem makes your work easier in many cases. I hope you liked the articles. Please leave a comment and give your valuable suggestions. Goodbye!

## Related Articles

- How to Compare Two Columns or Lists in Excel
- Excel Formula to Compare and Return Value from Two Columns
- How to Compare Two Lists and Return Differences in Excel
- Comparing Two Columns and Returning Common Values in Excel
- How to Compare Text in Two Columns in Excel
- Compare Two Columns in Excel and Highlight the Greater Value
- How to Compare Two Columns in Excel for Missing Values
- Statistical Comparison of Two Data Sets in Excel
- Macro to Compare Two Columns in Excel and Highlight Differences
- Excel Macro to Compare Two Columns

the same can be done with SUMPRODUCT: SUMPRODUCT((A2:A12=F2)*(B2:B12=F3)*C2:C12)

Better formula as you can compare more than just two cell, the only downside is that it will return with “0” and not #N/A which cause an issue if you are comparing data from two different time.

Thanks, Emil!

Freat, I loved it.

Thanks.

How To Compare Two Columns with the single-cell And Return in multiple Values From The second Column In the single cell of Excel?

Hi MAHENDRA TRIVEDI! You can use the following formula to compare two columns and return multiple matches in a single cell:

`=TEXTJOIN(",",TRUE,IF(A5:A15=D5,B5:B15,""))`

Here,

A5:A15= Range for matching criteria

B5:B15 = Range of the values to return

D5= Lookup value

TRUE ignores all the empty cells.

Check the 3rd case of the following article to know more details.

hhttps://www.exceldemy.com/index-match-return-multiple-values-vertically/

Thanks for being with us. Best regards.

I am keeping a note of your problem. Thanks.

Great work. Thanks.

Thanks, Uche.

Hi. Thanks for this. Can you use this formula when the looked up values are on a different sheet ?

If you’ve some specific problem, let us know via email, we shall try to make a blog post with your problem.

Thanks for this however when I try to run this on my data I always get a #VALUE! error on the second column range of data

https://i.imgur.com/ysJTNYR.png

Nevermind dont know what changed but I got it working. thanks again!

That’s great Griffin, I hope you get benefited from our articles.