Getting the value using VLOOKUP for multiple criteria columns

As you all know VLOOKUP is designed to lookup for a value in a set of data. This works fine if you are sending single lookup value. But if you have a table like below where column B or column C are not unique when we consider them individually. So if we look for a brand RANGEROVER, the VLOOKUP returns the value 3,500. This is fine if the year is 2014, but if the year is 2015 still the VLOOKUP returns 3,500 only because that is the first occurrence of RANGEROVER. To get the amount corresponding to brand RANGEROVER and YEAR 2015, we should make use of a helper column as shown below (column A). Column A is addition of column A and column B and to separate we put an underscore in between (you could use CONCATENATE function also to add the data here).

vlookup1

Same way while calling the VLOOKUP, instead sending one value BRAND, send the BRAND+YEAR as shown in the screenshot below.

So that formula will be  =VLOOKUP(I4&”_”&I5,$A$2:$D$100,4,FALSE)

vlookup2

Same result can be achieved using the combination of INDEX and MATCH functions as well.

 


Leave a Reply

Your email address will not be published. Required fields are marked *