Lookup across multiple worksheets
 

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

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


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=399630

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(TRU E,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"
wrote in message
news:DCSwearingen.1ue6ej_1125093940.7609@excelforu m-nospam.com...

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

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


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=399630

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!!


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=399630