Formula With Multiple Criteria -XLOOKUP

Brass Contributor

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

4 Replies
@Hans Vogelaar you've helped with something similar, do you think you could help with this one. It doesn't have to be an XLOOKUP

@CatherineMadden 

I Hope it help you 
=XLOOKUP($A$6&C2,'12-4'!$A:$A&'12-4'!$C:$C,'12-4'!$K:$K,0,0)

@CatherineMadden 

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.

@CatherineMadden 

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.