How to create an Index Match Formula for multiple sheets?

nlarson_PMC

Hi,

I am currently trying to create an Index Match function which will be able to search multiple sheets (5 to 6 separate sheets) for the correct information.

The destination sheet has two columns that it will look at.

1. A "TIDC" column where staff members place a ticket code
2. An "Assigned to" column, which is a contact list column

If I were just looking at one sheet the formula would look something like:

=INDEX({Sheet1 Assigned To},MATCH(TIDC@row,{Sheet1 TIDC},0))

I have tried to stack IFERRORs as well as nest IF formulas but it does not seem to function properly.

Any help would be very much appreciated.

Thank you,

Nicholas

Paul Newcome

Stacking IFERRORS would look like this:

=IFERROR(IFERROR(IFERROR(INDEX({Sheet1 Assigned To},MATCH(TIDC@row,{Sheet1 TIDC},0)), INDEX({Sheet2 Assigned To},MATCH(TIDC@row,{Sheet2 TIDC},0))), INDEX({Sheet3 Assigned To},MATCH(TIDC@row,{Sheet3 TIDC},0))), INDEX({Sheet4 Assigned To},MATCH(TIDC@row,{Sheet4 TIDC},0)))

Paul Newcome

Stacking IFERRORS would look like this:

=IFERROR(IFERROR(IFERROR(INDEX({Sheet1 Assigned To},MATCH(TIDC@row,{Sheet1 TIDC},0)), INDEX({Sheet2 Assigned To},MATCH(TIDC@row,{Sheet2 TIDC},0))), INDEX({Sheet3 Assigned To},MATCH(TIDC@row,{Sheet3 TIDC},0))), INDEX({Sheet4 Assigned To},MATCH(TIDC@row,{Sheet4 TIDC},0)))

nlarson_PMC

Thank you @Paul Newcome !