Formula checking multiple worksheets

  • Thread starter sonic-the-mouse
  • Start date
S

sonic-the-mouse

Sorry Biff for my lack of explanation.

I am trying to look up a number that could be on any one of the twelv
worksheets. I hope this clarifies what I'm looking for.
 
B

Biff

Hi!

OK, this will work if the lookup tables on all the sheets have the same
layout.

Make a list of sheet names that need to be "searched". Say you put that list
in H1:H12. Give that range a name, something like SheetList.

The lookup value is entered in A1.

The lookup tables on all the sheets are in the range A1:C10.

This example looks up the value in column A and returns the corresponding
value from column 3 of the lookup table.

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:A10"),A1)>0,0))&"'!A:C"),3,0)

Biff
 
Top