Index/match - multiple sheets in a workbook

Hello, I am trying to get INDEX/MATCH to work for multiple sheets and then i have to get it to work on multiple workbooks.
In short we are given a list of IDs, about 80 IDs and we have to reference 4 workbooks with 4 sheets on each workbooks (16 sheets with about 1000 IDs on each sheet.)

Right now I am trying to get it to work with just one workbook
For 1 sheet i was able to get it to work.

Please Login or Register  to view this content.

B2 is the ID i am trying to find, L:L is the column its located in sheet1 and F:F is the information that i need to pull from sheet1.

But that only works if i know where the ID is located and most times i do not, the ID that i need to find could be on sheet1, sheet2, sheet3, or sheet4 in workbook1.


I am trying to find a way to combine these:

Please Login or Register  to view this content.

Maybe some sort of IF statement within. IF it doesnt find the ID in sheet1 column L:L, then look for it sheet2 column L:L, so on and so forth. I just dont know how to put the function within that IF statement.

wow, you guys (maybe gals) are the best!
That worked awesomely awesome. I used ChemistB approach and cleaned it up a bit with FDibbins post.

I'm not sure if i need to check something for it to be solved but its solved.

Thank you, done.

Originally Posted by ChemistB

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save