# Vlookup multiple sheets

#### jfjl

##### New Member
hello. anyway i can vlookup to multiple sheets in a workbook ?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
what are you trying to do exactly?

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 ?

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???

YES. how do i do that ?
txtx

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)

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 ?

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.

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)

Replies
27
Views
346
Replies
9
Views
247
Replies
36
Views
574
Replies
5
Views
253
Replies
2
Views
122

1,196,055
Messages
6,013,135
Members
441,750
Latest member

### 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.

### Which adblocker are you using?

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

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