Index-Matching same value but from multiple worksheets

[tstage]

Greetings -

I have no problems index matching from one worksheet so that the desired output posts in a column cell in another worksheet. What I haven't been able to figure out yet is how to write a formula to check more than one worksheet for the same value.

Here's my scenario:

1. I'm trying to locate a single device with a unique serial number, which I will call "Device 0001"
2. Device 0001 can be located at any one of 16 different site locations at a given time but only at ONE location at a time
3. My workbook contains 16 worksheets (one for each site location) and EACH worksheet is identical. In other words, the same columns (e.g. Column M and Column C) in each worksheet are searched.
4. When Device 0001 is located, I want the formula to tell me its status at the site location

Here is the formula that works for the Site 1 worksheet and gives me a blank if Device 0001 ("C6") is not found:

=IFERROR(INDEX('[Site 1]'!M$14:M$100,MATCH(C6('[Site 24]'!C$14:C$100,0)),"")

Let's assume that Device 0001 is not at Site 1 but is at Site 2, which I'll already know because it's identified in another column called "Current Location". However, the formula doesn't know that so I want it to search for Device 0001 in both worksheets (identical columns) until it locates it. If Device 0001 isn't located at either site then I want the cell to return blank.

So, how would I alter the above formula so that it searches more than one worksheet. Ultimately, I need it to have the capability to search all 16 worksheets (or site locations). Again, the device will only be found in ONE worksheet/site location at a given time.

Many thanks ahead of time for the help and expertise...

Tom

 

[gaz_chops]

Duplicate post

 

[Fluff]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).