Vlookup

[NILON]

I am working in an excel workbook where the first worksheet is the master worksheet followed by worksheets for each location. I want to do a VLOOKUP to match the the code in the first column of the master worksheet to the codes in the location worksheets (also in the first column). The data I want to pull into the master worksheet is in a different column in each of the location worksheets. Is there a way to setup the function so that it will pull from different columns in each worksheet without having to setup a different formula for each location in the master worksheet? For example, the information I need for location A is in column Q and the information needed for location B is in column S. I want to pull from the different columns using only one formula in the master worksheet.

 

[prabby25101981]

If you have the names of the worksheets as headers, and you want to use VLOOKUP, here's the formula you'd need to deploy -

=VLOOKUP(A2,INDIRECT(CONCATENATE("'",$B$1,"'!A:B"),1),COLUMN(B$1),0)

Assumptions -
1. B1 includes the sheet name;
2. A2 is the value you are looking up in each sheet
3. I have taken only two columns here (A:B). You need to change it to the number range of columns till where you have the data.
4. The column # will change automatically to the column where the formula is deployed.

Hope this helps !

Prabby