Perform Two-dimensional lookups
Under usual circumstances while using VLOOKUP we hardcode the column number from which Excel should get the result. Consider the following formula:
=VLOOKUP(G5,B2:C10,2)
“2” is actually coloumn number that we mentioned. But we may have situations where our data is spread in different columns, and we need to get the right column first based on criteria and then we perform the lookup. This is called 2D lookup or two-dimensional or 2 criteria lookup. It goes with many names.
Consider the following situation:
What we want is to extract the price of mentioned product AND size. Now as the sizes and their prices are mentioned in different columns, we will involve another lookup function named MATCH to pair up with VLOOKUP.
Here is the formula:
=VLOOKUP(I8,B5:E14,MATCH(I9,$B$5:$E$5,0),FALSE)
MATCH function is helping us identify the right column. In our case “M” size is second column. MATCH function will return “2” by looking up the size in B5:E5 range. Once we have the column number VLOOKUP will fetch the correct price for us.
Thanxxx….. It is help lot of me..
Sorry but {=VLOOKUP(B20,B7:F15,{2,3,4,5},FALSE)} doesn’t work. I use (;) and result is always S M L XL
pants 18,00 18,00 18,00 18,00
Why ?
Salam Hassan Sir, I can not resist to inspire from your work. I am student cum employee. Excel is badly need for me. I want to learn excel with a full exposure. I am living in Rawalpindi, is there any possibility to teach me excel.?