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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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