Forum Discussion
XLOOKUP using 3 unique criteria
For your cost-related columns, use this formula structure:
=XLOOKUP(1,
(Transactions!$A$3:$A$1000=$A$4) * (Transactions!$H$3:$H$1000=AN5),
XLOOKUP(Transactions!$O$2, Transactions!$A$2:$BA$2, Transactions!$A$3:$BA$1000))
If you prefer a FILTER-based approach:
=FILTER(
XLOOKUP(Transactions!$O$2, Transactions!$A$2:$BA$2, Transactions!$A$3:$BA$1000),
(Transactions!$A$3:$A$1000=$A$4) * (Transactions!$H$3:$H$1000=AN5),
"No match found")
If your cost columns in Dashboard (AO:AS) have headers matching the Transaction sheet headers, you could use:
=XLOOKUP(1,
(Transactions!$A$3:$A$1000=$A$4) * (Transactions!$H$3:$H$1000=$AN5),
XLOOKUP(AO$4, Transactions!$A$2:$BA$2, Transactions!$A$3:$BA$1000))
Where AO$4 contains the header name for that cost column (then drag across).
Thank you for your suggestions. I also posted this on another Excel forum and got the following suggestions:
Listing the carriers: (This still works as intended) - Note: No slowdown noticed when using this formula alone.
=FILTER(Transactions!H3:H1000, Transactions!A3:A1000=A4, "No carriers found")
Cost related columns: (This too works as needed, but now the workbook has really slowed down)
=XLOOKUP(AN4&Policy,Transactions!H:H&Transactions!A:A,Transactions!O:O,"")
Because of the slowdown, I will try your suggestions tonight to see if it brings the speed back up. In the meantime, I would appreciate any insight you may have as to why this xlookup has bogged my workbook down. I do realize that there is a good amount of additional data gathering going with this new section though. The slowdown is when I use the dropdown (Dashboard!A4) to pick a policy. It takes a good 3 - 5 seconds for the data to adjust throughout the entire Dashboard (not just this new section). Even a copy/paste on a different sheet takes a few seconds and always brings up the hourglass for that few seconds too. I will also ask the person that suggested this formula to see if he has any ideas on this too.