# Formula With Multiple Criteria -XLOOKUP

Brass Contributor

# 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

4 Replies

# Re: Formula With Multiple Criteria -XLOOKUP

@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

# Re: Formula With Multiple Criteria -XLOOKUP

=XLOOKUP(\$A\$6&C2,'12-4'!\$A:\$A&'12-4'!\$C:\$C,'12-4'!\$K:\$K,0,0)

# Re: Formula With Multiple Criteria -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.

# Re: Formula With Multiple Criteria -XLOOKUP

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.