Lookup across multiple worksheets

D

DCSwearingen

Is it possible to use a lookup function across multiple sheets?

Example:
A workbook contains 26 sheets. Can a function on Sheet1 look acros
sheet2 through sheet26 and if sheet9 cell A1 contains a specific valu
(only one of the sheets can contain the value) return the contents o
sheet9 cell A2
 
B

Biff

Hi!

Yes, it's possible if you're meaning a V or H lookup. Sounds like you need a
HLOOKUP.

The lookup range must be EXACTLY the same on ALL of the sheets.

Create a lst of your sheet names and give that list a name. Something like
SheetList

Enter this formula using the key combo of CTRL,SHIFT,ENTER:

=HLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A1:E1"),A1)>0,0))&"'!A1:E5"),2,0)

The lookup value is in cell A1. The lookup range on each sheet is A1:E5. The
row_index_number is 2.

Biff

"DCSwearingen" <[email protected]>
wrote in message
news:D[email protected]...
 
D

DCSwearingen

Thanks for the tip!

Sounds like an array formula using a named list.

I am not familiar with list names, but I am going to see what I can
find and try to make this work for me.

Thanks for the help!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top