Lookup over multiple sheets

If you have the morefunc.xll add-in installed...

1. Insert two new sheets, name them First and Last.
2. Place all the relevant sheets between First and Last.
3. Invoke on Sheet1...

=INDEX(THREED(First:Last!$E$2:$E$400),MATCH(A1,THREED(First:Last!$D$2:$D$400),0))

This is for people at work, at a Government office

I need to do it without add ins

Can this still be done?

Kelly

Yes, it can be done without the addin.

(From one of Domenic's solutions)

Try:
=VLOOKUP(A1,INDIRECT("'"&INDEX(TabNames,MATCH(TRUE,COUNTIF(INDIRECT("'"&TabNames&"'!D2:D100"),A1)>0,0))&"'!D2:E100"),2,0)

Note:
TabNames is a named range including the names of each of the sheets to lookup. You must list the sheet names somewhere on the sheet and name the range, TabNames.
D2:D100 is the Lookup Range
D2:E100is the Lookup Table to pull info from
A1 is the lookup value

Adjust ranges to suit as necessary

The formula Must be confirmed with CTRL+SHIFT+ENTER, not just Enter