How to Match Two Columns and Return a Third in Excel

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.


Download Practice Workbook

Download this workbook and practice while going through this article.


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

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.

Dataset for match two columns in excel and return a third


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)

VLOOKUP to match two columns in excel and return a third

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.

VLOOKUP to match two columns in excel and return a third


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.

INDEX-MATCH Functions VLOOKUP to match two columns in excel and return a third

  • Finally, use the Fill Handle to AutoFill up to F9.


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.

INDEX-MATCH Functions VLOOKUP to match two columns in excel and return a third

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.

Use of INDEX, MATCH and IF Functions


Things to Remember


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 will like this article. Stay tuned for more valuable articles.

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

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

  2. Freat, I loved it.

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

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

  5. 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

Leave a reply

ExcelDemy
Logo