The VLOOKUP function in excel does not handle multiple criteria by default. However, with little tweaking the source data or with the help array formula we can perform multi condition lookup. In this post, we will learn the way to do two column lookup on Excel.
Let us take a sample data.
To make this formula we have defined user named ranges.
Range(C4:D6) is named as lookup_Table
Other related posts:
VLOOKUP in EXCEL Tips and Tricks
Perform two column multi condition vlookup using the helper column
As VLOOKUP does not take multiple criteria, we need to insert a new column called helper Column. In this column we need to concatenate the fields of two column. In our example, we have concatenated Brand and Model using the formula concatenate or with the help of “& “.
Formula in cell C4 is
=A4&B4
Final vlookup formula is
=VLOOKUP(G3&G4,lookup_Table,2,FALSE)
How this conditional vlookup works
In the example, we want to lookup price using vlookup based on brand and model.
One of the limitation of VLOOKUP is it handles only one condition. The lookup value will be searched in first column of the selected data range. To overcome this limitation, we are using the helper column.
In the helper column, we have concatenated the Brand and model. The lookup value also we have joined together. In the example, VLOOKUP searches for BajajPulsor in the range C4 to C16. It is there in 9 the row. Now it will return the corresponding value in 2nd column. That is 53000.
Leave a Reply