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

ExcelNoelAga

New Member
Joined
Aug 15, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • sheet PAC.png
    sheet PAC.png
    32.2 KB · Views: 8
  • Sheet ATL.png
    Sheet ATL.png
    28.6 KB · Views: 9
  • Deployment Master Tracket sheet.png
    Deployment Master Tracket sheet.png
    30.4 KB · Views: 9

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
maybe not the cleanest solution but appears to work
Excel Formula:
=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))
))
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top