Performing two column lookup in Excel:This example uses User named ranges as mentioned below
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.
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’.