Forum Discussion

CatherineMadden's avatar
CatherineMadden
Brass Contributor
Dec 28, 2023

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)...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 28, 2023

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.

Resources