Vlookup across multiple sheets

[Sweedler]

Hello all

I have an interesting conundrum.

I have a document with about 20 sheets and want a Vlookup on one of them that searches all the others. My thought was to create a little sub-sheet that gives me the name of the sheet based on the cell value that dictates which sheet to search. But I can’t figure out how to incorporate a cell value into my vlookup “range”.

Does anyone know how to do this?

I’m open to other ideas for how to do the multiple sheet vlookup as well.

Thank you

 

[mohadin]

Hi
how about

=VLOOKUP($A$4,INDIRECT(INDIRECT("B2")&"!E5:F17"),2,0)

 

[Sweedler]

Hi
how about

=VLOOKUP($A$4,INDIRECT(INDIRECT("B2")&"!E5:F17"),2,0)

Works brilliantly. I knew there had to be a way.

Nice job buddy

 

[Sweedler]

Hi
how about

=VLOOKUP($A$4,INDIRECT(INDIRECT("B2")&"!E5:F17"),2,0)

But there is one problem. The CELL (B2 here) does not alter as I drag the formula through. And that is a problem as I need the formula in about 20000 cells .. but with a new cell for each one.

 

[Fluff]

How about

=VLOOKUP($A$4,INDIRECT("'"&B2&"'!E5:F17"),2,0)

 

[Sweedler]

How about

=VLOOKUP($A$4,INDIRECT("'"&B2&"'!E5:F17"),2,0)

That one got it.

Dynamic

 

[Fluff]

Glad we could help & thanks for the feedback.