Looking for Excel formula to lookup multiple criteria and in multiple sheets

[ExcelNoelAga]

I have a master sheet (DeploymentMasterTracker) where I enter my formulas to get the values from other sheet within my document.

I have this formula: =IF(INDEX(ATL!E:E,MATCH(B5&DeploymentMasterTracker!C2,ATL!A:A&ATL!D:D,0))="complete",(IF(INDEX(ATL!E:E,MATCH(B5&C3,ATL!A:A&ATL!D:D,0))="not started","In progress",INDEX(ATL!E:E,MATCH(B5&C3,ATL!A:A&ATL!D:D,0)))),INDEX(ATL!E:E,MATCH(B5&DeploymentMasterTracker!C2,ATL!A:A&ATL!D:D,0)))

that provides me the value I seek from the sheet titled "ATL"; however, if the value returns #N/A (because it cannot match the value I8 from ATL!B:B), I want the system to search in another sheet (in the same worksheet) called "PAC" where it would use the same formula logic, and again if the value returns #N/A, I want to perform another search in a third sheet titled "PRA".

Hopefully this make sense.

 

[ExceLoki]

maybe not the cleanest solution but appears to work

=IFERROR(IFERROR(
IF(INDEX(ATL!E:E,MATCH(B5&DeploymentMasterTracker!$C$2,ATL!A:A&ATL!D:D,0))="complete",(IF(INDEX(ATL!E:E,MATCH(B5&DeploymentMasterTracker!$C$3,ATL!A:A&ATL!D:D,0))="not started","In progress",INDEX(ATL!E:E,MATCH(B5&DeploymentMasterTracker!$C$3,ATL!A:A&ATL!D:D,0)))),INDEX(ATL!E:E,MATCH(B5&DeploymentMasterTracker!$C$2,ATL!A:A&ATL!D:D,0))),
IF(INDEX(PAC!E:E,MATCH(B5&DeploymentMasterTracker!$C$2,PAC!A:A&PAC!D:D,0))="complete",(IF(INDEX(PAC!E:E,MATCH(B5&DeploymentMasterTracker!$C$3,PAC!A:A&PAC!D:D,0))="not started","In progress",INDEX(PAC!E:E,MATCH(B5&DeploymentMasterTracker!$C$3,PAC!A:A&PAC!D:D,0)))),INDEX(PAC!E:E,MATCH(B5&DeploymentMasterTracker!$C$2,PAC!A:A&PAC!D:D,0)))),
IF(INDEX(PRA!E:E,MATCH(B5&DeploymentMasterTracker!$C$2,PRA!A:A&PRA!D:D,0))="complete",(IF(INDEX(PRA!E:E,MATCH(B5&DeploymentMasterTracker!$C$3,PRA!A:A&PRA!D:D,0))="not started","In progress",INDEX(PRA!E:E,MATCH(B5&DeploymentMasterTracker!$C$3,PRA!A:A&PRA!D:D,0)))),INDEX(PRA!E:E,MATCH(B5&DeploymentMasterTracker!$C$2,PRA!A:A&PRA!D:D,0))
))