Last updated on June 11th, 2018
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
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 1st column.
Let’s look into the below table where we have some product IDs along with its 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 function together. IT will give us the same output like 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 of the 2 values that will be given. Here we will be using a formula which contains the INDEX and MATCH Function. The below image shows the information of 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
Comparison between columns to find matches and resulting a value from a different column is a common practice in Excel. By knowing the solutions to this kind of problems makes your work easier in many cases. Hope you will like this article. Stay tuned for more useful articles.
- How to Compare Two Columns in Excel For Finding Differences
- How to Compare Two Columns or Lists in Excel
- How to Compare Two Columns in Excel Using VLOOKUP
- Excel formula to compare two columns and return a value (5 examples)