Fetch values from multiple columns in one go
So far we have been fetching result from one specified column that is either manually hard-punched by us in VLOOKUP formula or we determined it using MATCH function. But we can fetch values from multiple columns at once against single a lookup value.
Here is one situation where products have different sizes and you want to know the prices of all sizes of selected product.
This requires fetching the prices from all the columns at once using array formula. It requires two main steps:
- we need to pre-select the range of cells where we want the prices for each size for specified product
- we need to punch in the formula using CTRL+SHIFT+ENTER combination instead of simple Enter key press.
The formula used is following:
{=VLOOKUP(B20,B7:F15,{2,3,4,5},FALSE)}
Remember the curly brackets are not inserted by me rather they represent that formula is entered as an array formula using CTRL+SHIFT+ENTER combination.
Following illustration walk you through the process:
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.?