Multiple worksheet vlookup

[Anto111]

Hi guys,

I have a workbook containing 46 sheets and I am trying to use VLookup to
return a valiue on a summary sheet when one of the specific worksheet names
is entered in cell K2

The formula I am currently trying is highlighted below however it is
returning #REF!

=VLOOKUP(K2,THREED('[Heart Rate.xlsx]Week 1':'[Heart Rate.xlsx]Week
46'!$D$7:$NF$74),9,0)

The data to be looked up is located in a seperate workbook to the summary
sheet and the workbook is called "Heart Rate".

The worksheets within this workbook are labled "week 1" thorugh to "week 46".

Any help would be fantastic.

Many thanks in advance,

Ant

 

[igorek]

you can only do it via macro as vlookup and other lookups do nut support
ranges on multiple ranges...

 

[L. Howard Kittle]

Hi Ant,

The Look_Up value (K2) cannot be a worksheet name, it has to be a value,
text or such.

I have a multi-sheet lookup that Peo Sjoblom shared with me, it may do what
you want. It looks up across sheets the SAME workbook. I have not tested
Peo's solution to look up in another workbook but have had success using a
simple VLOOKUP formula to do that, but not across multiple sheets in that
workbook.

Even with some e-mailed details on how it works I cannot figure it all out.
The example he sent me covered 8 worksheets, but that is just a matter of
the number of sheets you list in the named range MySheets. The first
formula is the original from Peo, the second is the same which I modified to
trap errors and return "".

The first one looks up the value of A2 on the main sheet and searches all
sheet names in MySheets A2:A200 in each and returns the third column value.
The second one does essentially the same thing with error trapping and looks
up the value in C3 and returns column 2 value.

You will need to make a list of all the sheets you want to look up and name
it MySheets (or whatever, if other than MySheets then use your name in the
formula instead of MySheets).

Of course adjust the ranges to suit your sheet needs.

Use Ctrl+Shift+Enter to commit the formula. (Array Enter)

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

=IF(ISNA(VLOOKUP(C3,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)>0),0))&"'!A2:F58"),2,0)),"",VLOOKUP(C3,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A2:F58"),C3)>0),0))&"'!A2:F58"),2,0))

Looks a bit intimidating but works great. If it does not make sense, you can
send me an example workbook with detailed instructions on what you want to
happen, and I will give it a go.

HTH
Regards,
Howard