Excel Table Lookup with OR Conditions and Multiple Columns

[BenW71]

BACKGROUND:
I have a PowerBI app that combines a number of data sources into a file with ~75 columns (leftmost 40 or so are data columns, the rest are equations). There's about 300k rows in the file. MANY of those columns contain calculations referring to other rows, and some of them excel functions like SumIFS, XLookups, etc. Excel is basically dying on some pretty beefy computers with the equations in the spreadsheet itself. If you try to sort it, change the selection within an autofilter, open/close the file, or blink twice it seems to want to recalculate all cells in the file. If that recalculation finishes it's generally 10-30 minutes.

NOTE: Sorry, i can't share the full file - my company is very restrictive with that type of thing. Anyway i'm looking for help with just one part not tuning of the full file...

We are in the process of moving all of the equations from the excel file into the PowerBI process.

MY CHALLENGE:
One of the columns has a business case where I need to find a value associated with four different criteria (in columns) in a lookup table. I basically have to have excel go down the table pictured below and find the first match.

• I have coded this already in a macro (which we run after the PowerBI data is loaded into the spreadsheet with the equations) and it takes a long time.
• I turn off autosave and auto calc before and after the procedure is run for the bulk update.
• I put timers into the code to watch progress; the macro runs in about 3 minutes, but turning off and on autosave and autocalc is SIGNIFICANTLY longer - up to 30 minutes for those parts.

Therefore I wanted to move this into an excel equation. I could also put it in PowerBI process (via DAX). If it was in excel i'd have more usability outside of this one use case however...

Here are some areas of complexity i haven't been able to figure overcome:

• I really need to be able to use "All" for cases where the specific criteria should be match by ANY value.
  • This is because there are only a few exceptions within each "org", for example, ALL of the organizations should have 0.0 for "Out of Office" or "Support". All digital large projects should be 7, and small projects 3.5.
  • There are a LOT of Business Units, and they can change over time. so doing a cross matrix (ie having each combination of the 4 criteria as its own row) would result in thousands of rows which need to be maintained and is not viable.
• I haven't been able to figure out a way to do an XLOOKUP or Match or whatever that would look for the first row based on a combination of all 4 values AND using a matching value of EITHER the value for that row (ie that specific org) or the word "All"... ANY value in that column should match an "All" entry in that table...

I have the table and some test data below:

Thanks!

 

[BenW71]

The expected results in that table were wrong on the lines (cut and paste :/ )