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. In Excel, there are many find and match functions like FIND, MATCH, INDEX, VLOOKUP, HLOOKUP, etc. Here in this article, we are going to use some of these.

**Table of Contents**hide

## Matching Two Columns with Same Values to Return a Third Column (VLOOKUP)

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 1^{st} column.

Letâ€™s look into the below table where we have some product IDs along with their corresponding prices. We create another column with a heading of **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 **Price-2** column.

Here we will use a formula by which we will get the corresponding price value of different product IDs. Write the formula **=VLOOKUP(D2,$A$2:$B$12,2,FALSE) **in cell E2.

After pressing Enter you will get the below result.

Now, drag down the formulated cell E2 below from E2 to E6 to find out the result for the entire column.

## Matching Two Columns with Same Values to Return a Third Column (MATCH & INDEX)

The previous problem can be solved with a different formula. In this formula, we will be using the INDEX and MATCH functions together. IT will give us the same output as the previous method.

To perform this write down the formula **=INDEX($B$2:$B$12, MATCH(D2,$A$2:$A$12))** in cell E2.

After pressing enter you will get to see the below result.

Now like before drag down this formulated cell in downwards to insert the formula for the whole column.

## Using INDEX and MATCH Function to Match Two Columns in Excel and Return a Third

Let`s work on a different example where we will use two values from two different columns where the values exist in the same row. The return value will be from the third column and it will also be in the same row as the 2 values that will be given. Here we will be using a formula that contains the INDEX and MATCH Function. The below image shows the information on the example that we will be working on.

Write down the formula, **=INDEX(C2:C12,MATCH(F2,IF(B2:B12=F3,A2:A12),0)) **in cell F4.

After writing the formula press **Ctrl + Shift +Enter** to use it as an array formula. You will see a pair of 2nd brackets appear in the formula which contains the formula inside it. After doing this you will get to see the below result.

Here the formula compares two values **PID-1001** from column **Product ID** and **C** from column **Category. **From this, the resultant value is **2186** which is in the same row of these two values. By changing the cell values of cell F2 and F3 we will get different results in cell F4.

## Download The Working File

## Conclusion

A comparison between columns to find matches and resulting in a value from a different column is a common practice in Excel. By knowing the solutions to this kind of problem makes your work easier in many cases. Hope you will like this article. Stay tuned for more useful articles.

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?

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.