Forum Discussion
Formula With Multiple Criteria -XLOOKUP
Attached is a report example that I was wanting help with a formula. I was going to use an XLOOKUP but need multiple criteria. I was trying to do an XLOOKUP if:
1. If (Totals) A3 is found in (12-4) A:A and
2. (Totals) B2 is found in (12-4) B:B then
3. Return column (12-4) J:J
- HansVogelaar you've helped with something similar, do you think you could help with this one. It doesn't have to be an XLOOKUP
The layout of the 12-4 sheet makes it complicated for a formula. I have attached a workbook with a custom VBA function GetEP. You'll have to allow macros when you open it.
I Hope it help you
=XLOOKUP($A$6&C2,'12-4'!$A:$A&'12-4'!$C:$C,'12-4'!$K:$K,0,0)As variant that could be
=LET( BlockStart, XMATCH($J3, '12-4'!$A:$A) + 2, BlockSize, XMATCH( , INDEX('12-4'!$C:$C, BlockStart):INDEX('12-4'!$C:$C, BlockStart + 33) ) - 2, monthPrefix, MONTH(1 & INDEX('12-4'!$A:$A, BlockStart)) & "-", days, monthPrefix & TAKE(DROP('12-4'!$B:$B, BlockStart - 1), BlockSize), ep, TAKE(DROP('12-4'!$J:$J, BlockStart - 1), BlockSize), XLOOKUP(TEXT(K$2, "mm-d"), days, ep, 0) ), table to the right in attached.
