Vlookup across mulitple sheets

[suarezri]

Afternoon,

I'm trying to use a Vlookup to look if a number appears in the same single column (D) across 88 worksheets. I'm then hiding this cell and using a conditional format in the adjacent cell with an =IF(O4>0,"Y") to let me know the value has been found. I'm sure this isn't the most efficient way to do this, but I can't figure out another way.

If I use:

=IFERROR(VLOOKUP(J4,'Smith, John'!D2:D101,1,FALSE),IFERROR(VLOOKUP(J4,'Smith2, John'!D2:D101,1,FALSE),IFERROR(VLOOKUP(J4,'Smith3, John'!D2:D101,1,FALSE),IFERROR(VLOOKUP(J4,'Smith4, John'!D2:D101,1,FALSE),IFERROR(VLOOKUP(J4,'Smith5, John'!D2:D101,1,FALSE),IFERROR(VLOOKUP(J4,'Smith6, John'!D2:D101,1,FALSE),"")))))) it works fine, but I can only have the 64 nested if statements, so I'll run out.

I've tried several variations of creating an array on the same worksheet "MySheets" and listing the range, but I get a ?Name error.
=VLOOKUP(B1,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!D2:D101"),J4)>0),0))&"'!D2:D101"),1,False)

Any tips? I'm stumped.

[tranhaithang]

Can you post an example in here?, my first thought is using Countif

[suarezri]

Lookup_Value: H4 (an 8 digit number, that will never be duplicated anywhere in the workbook)
Table_Array: Only Column D of: 'Smith1, John' 'Smith2, John' 'Smith3, John' etc for 88 total sheets
Col_index_num: 1 (I think, it's the only column I need)
Range_lookup: False so it finds an exact

[martindwilson]

are they your exact sheet names?

[Aladin Akyurek]

Control+shift+enter, not just enter:

Please Login or Register  to view this content.

This would return B1 if B1 is available, provided that MySheets lists all of the relevant sheets and the sheet names are all text.

[suarezri]

are they your exact sheet names?

Not exactly, the real sheets names are real people's names.

[Aladin Akyurek]

Not exactly, the real sheets names are real people's names.

See post #5...

[suarezri]

Control+shift+enter, not just enter:

Please Login or Register  to view this content.

This would return B1 if B1 is available, provided that MySheets lists all of the relevant sheets and the sheet names are all text.

When I use this:

=VLOOKUP(I4,INDIRECT("'"&LOOKUP(REPT("z",255),IF(COUNTIF(INDIRECT("'"&MySheets&"'!D2:D101"),I4),MySheets))&"'!D2:D101"),1,0) I'm still getting a #NAME? in the cell. I have all of the sheets off to the side (that will be hidden) listed under a cell listed MySheets, is this the correct manner?

When I show the calculation steps it shows the evaluation shows the 255 z's...

[Aladin Akyurek]

You need to create a range which houses all of the relevant sheets one by one. When done, select this range and name the selection MySheets using the Name Box. That will take care of the #NAME? error...

[martindwilson]

works fine for me are you sure there are no gaps in the formula?

Please Login or Register  to view this content.

[suarezri]

I found my error, I didn't name my range of sheets. It works perfectly now!

[Aladin Akyurek]

I found my error, I didn't name my range of sheets. It works perfectly now!

Great to hear it's sorted out.