Vlookup multiple sheets

jfjl

New Member
Joined
Mar 5, 2009
Messages
10
hello. anyway i can vlookup to multiple sheets in a workbook ?
thanks in advance !
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
in workbook A
i have got IDs with their respective zones . (N,S,E,W)

in workbook B , the zones are messed up, ie there are multiple sheets, labelled E1,E2,E3 so on so forth .


how do i vlookup the IDs from workbook A to all the sheets in workbook B ?
 
Upvote 0
well.... it seems to me that the data that you have is on workbook A and you want all of the sheets in workbook B to look at workbook A for the vlookup???
 
Upvote 0
here is one example...not knowing where your ranges are or your book names you will have to adjust accordingly..... you need to put this formula in all of your B workbook worksheets hope that makes sense

=VLOOKUP($A4,[BookA]Sheet1!$A:$G,2,FALSE)
 
Upvote 0
Hi ,

afraid its the wrong way .

Workbook A goes like this

ID ZONE VALUE
1234 E To be looked up in WORKBOOK B

workbook B has multiple sheets labelled E1 E2 E3 E4 E5 so on so forth .which contain various IDs and their resp values.


how do i extract values from workbook B , not knowing which sheet the ID is under ?
 
Upvote 0
sorry i just checked that link and it is now dead... doing a google search i found this... it may help you on your way

Here is the formula and must be array entered (Ctrl + Shift + Enter).

=VLOOKUP(A2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),3,0)

Where: A2 is the look-up value
Where: MySheets is a named range consisting of a list of all the worksheet
you want to look up.
Where: A2:C200 is the lookup array of each sheet.

This will return the lookup value of column C that the lookup value finds on
whatever sheet, note the 3 just before the ,0) at the end of the formula.

You will need to adjust the ranges to suit your workbook.

This is what the formula would look like if you did not use a named range
for the worksheets but instead entered the worksheets names in the formula.
Much better to use a named range. Note that in this formula it returns the
value in Column B per the 2 at the end.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top