Value lookup from multiple tabs

**rmantoo** · 03-15-2010, 04:45 PM

First, I've been through 100s of searches here and on other excel forums, and cannot find this. It seems like it should be simple, but I'm getting nowhere.

If' I'm not using correct forum procedure, or Excel terminology, I apologize in advance: I've used Excel for years, but honestly am only recently starting to modify files.

In each "Daily"(where I have one Daily per date) tab/sheet, I have:

A C

24 BHA #

25 PDM Serial#

I then have 15 sheets labeled BHA 1, through BHA 15.

In each of the BHA sheets, I have:

A E

Description S/N

23 Motor (unique serial number)

In my "Daily" sheet, I want to be able to put a number (1 through 15) in C24 for whichever BHA I'm using that day, and have it then put the corresponding Motor serial number in C25

**NBVC** · 03-15-2010, 04:55 PM

Try this formula:

=INDEX(INDIRECT("'"&$A$24&$C$24&"'!A20:P43"),MATCH("Motor",INDIRECT("'"&$A$24&$C$24&"'!B20:B43"),0),5)

you would have to adjust the "Motor" keyword and the 5 (indicating column number) as appropriate for other items to retrieve.

**jj72uk** · 03-15-2010, 05:00 PM

Error

10 chars

**rmantoo** · 03-15-2010, 05:11 PM

Thanks for the response.

Evidently I didnt' phrase this correctly.

The number I want to retrieve is ALWAYS going to be in Cel E23, but in one of 15 possible tabs.

If the input in my Daily tab, cel C24 is any number, 1 through 15, I want to retrieve the value in E23 from the corresponding "Bha X" tab to be in C25 on that daily.

For example, if I input "2" in C24 on my daily, I want the value listed in worksheet "Bha 2", cel E23 to be in C25 on that daily.

If I input "4" in C24 on my daily, I want the value listed in worksheet "Bha 4", cel E23 to be in C25 on that daily.

If I input "12" in C24 on my daily, I want the value listed in "Bha 12", cel E23 to be in C25 on that daily.