Index match a cell to mutiple spreadsheets

[lwhyatt]

I am using the formula:

=INDEX('[NP8 Nov 2015.xls]EHC NDT Phase 2'!$E:$E,MATCH(B2,'[NP8 Nov 2015.xls]EHC NDT Phase 2'!$D:$D,0))

to find the occurrence of B2 in the worksheet of another spreadsheet '[NP8 Nov 2015.xls]EHC NDT Phase 2'. This works fine.

However if it doesn't find it there, I'd like the formula to go looking in '[NP8 Nov 2015.xls]EHC NDT Phase 1' then, if it still can't find it, look in '[NP8 Nov 2015.xls]EP NDT' My question is, how do I join these INDEX MATCH searches together into one formula?

 

[svendiamond]

How about something like:

=IFERROR(formula1,IFERROR(formula2,IFERROR(formula3,"")))

so:

=IFERROR(INDEX('[NP8 Nov 2015.xls]EHC NDT Phase 2'!$E:$E,MATCH(B2,'[NP8 Nov 2015.xls]EHC NDT Phase 2'!$D:$D,0)),IFERROR(INDEX('[NP8 Nov 2015.xls]EHC NDT Phase 1'!$E:$E,MATCH(B2,'[NP8 Nov 2015.xls]EHC NDT Phase 1'!$D:$D,0)),IFERROR(INDEX('[NP8 Nov 2015.xls]EP NDT'!$E:$E,MATCH(B2,'[NP8 Nov 2015.xls]EP NDT'!$D:$D,0)),"")))

 

[lwhyatt]

Great that works perfectly, I have even managed to nest in another formula! Presumably I can keep going on like that ad infinitum?

 

[svendiamond]

You're welcome. I think the limit on nested formulas is 64.