Vlookup

NILON

New Member
Joined
Jul 30, 2010
Messages
1
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.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
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 -

Code:
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,636
Messages
5,660,002
Members
418,541
Latest member
bugsoup

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top