INDEX and MATCH multiple worksheets

I am still very much a novice but I have used the INDEX and MATCH functions before and got them to work successfully but this one eludes me should I be using a different formula for this to work.

The formula will be in my main worksheet, to run down 10,000 rows and to pull information from each employee's record ie: Name, Dates etc daily instances from each employee.

This formula works with individual worksheets

=IF(ISNA(INDEX(BloggsA!A:K,MATCH(Serial_No,BloggsA!I:I,0),1))," ",INDEX(BloggsA!A:K,MATCH(Serial_No,BloggsA!I:I,0),1))

this pulls Name ie: ARTHUR BLOGGS

=IF(ISNA(INDEX(BloggsA!A:K,MATCH(Serial_No,BloggsA!I:I,0),2))," ",INDEX(BloggsA!A:K,MATCH(Serial_No,BloggsA!I:I,0),2))

this pulls Date ie: 29/10/2014

and so on, if there is no match the cell will be blank. In my main worksheet “Schedule” I have a Named Range Serial_No and in all the employee's worksheets I have in a column a dropdown list “Serial_No” by selecting a Serial_No the information from that row in the employee's sheet will populate the row with the matching number in the main worksheet. There is 36 sheets with each employees record, in a separate worksheet “Data” I have created a named range called “SheetList” and this is the formula that I tried with variations that I cannot get to work.

=IF(ISNA(INDEX("SheetList!A:K",MATCH(Serial_No,"SheetList!I:I",0),1))," ",INDEX("SheetList!A:K",MATCH(Serial_No,"SheetList!I:I",0),1))

Can anyone explain where I am going wrong I hope this makes sense.

Sixthsense

Many thanks but I could not get your formula to work.

XOR LX

I have attached a cut down version of the file I hope you can see what I'm after.

In any employee's worksheet I enter dates in the two columns and then in the column with the serial numbers I select a number it then populates the row that matches the number in the main worksheet "Schedule".

I have filled in the first five rows from individual worksheets which works, and the next five rows with "SheetList" in the formula which I cannot get to work, I need the formula to go through all the worksheets to find the matching number.

SpreadsheetTest.xlsx

Regards

Originally Posted by XOR LX

Thanks.

Are you saying that you simply want all non-blank entries from all five sheets to be populated in the Schedule sheet? There are no criteria to apply governing which of those records should be returned? Simply all?

XOR LX

Sorry if my explanation was not clear, the file I uploaded is a shortened version so if this works I can extend it to more columns. The file I uploaded was just to show that it works with one worksheet name in the formula. What I was hoping for is a formula to look for a match in all the employee's worksheets then show the result in one row for one employee in the "schedule" worksheet with the same Serial_No.

1. I fill in an employees dates their name is already in column "A" on their worksheet.

2. I then select a number in sequential order from the dropdown in column I:I on their worksheet.

3. Then on the worksheet "Schedule" where the formula's are they would populate the cells in the row with the matching Serial_No.


Regards