How to Perform two column Lookup on Excel

In earlier post, we have seen how to use simple vlookup on Excel, two way lookup comparing row and column  This post will show you how perform lookup comparing two column in Excel.  To perform lookup based on two column can be achieved in two ways. first one by concatenating two columns and the using vlookup and the other one is using Excel Array function. This method will help you to get the result when your matching criteria is on two columns. For Row and Column lookup read here.
Performing two column lookup in Excel:This example uses User named ranges as mentioned below
Range(A1:A11)-Range1
Range(B1:B11)-Range2
Range(C1:C11)-Price
Range(A15)-Brand
Range(B15)-Model

Vlookup examples

Method 1: This method shows how to perform two column vlookup by concatenating two column. Let us have look into sample tablet mentioned above.
Above example table is of two wheeler prices of different brand and models with price. Now we need to lookup for price based on brand and model. in the above example Column E contains the concatenated value of columns A &Column B. Column F contains same value of Column C. Once you create a table like this enter the formula as mentioned below.
=VLOOKUP(Brand&Model,E2:F11,2,FALSE) which returns 42000.
Method 2: Using Excel Array Formula: The below formula works by concatenating the content of Brand and model and then searching for this text in an array consisting of the corresponding concatenated text in Range 1 & Range 2.
=INDEX(Price,MATCH(Brand&Model,Range1&Range2,0))
This is a array formula so you need to enter Ctrl+Shift+Enter instead of just enter. If you just press “Enter” then value shows ‘#Value’.

Meet the Author

Hi, I am Aniruddha, an avid blogger since 2008 who likes writing about new gadgets, smartphones and excel. You can find them at www.tech2touch.com. You can also follow me on Twitter @tech2touch or my Facebook page tech2touch. You can subscribe to my YouTube channel tech2touch. Enjoy reading and watching!!!

Shares