How to return max date using XLookup

[deacon10]

Hi all,

I am trying to use an Xlookup to return the max date from column C (sheet2) where there is a matching value in column A (sheet1) against column B (sheet2). This is the formula for my two failed attempts below.

=IFERROR(XLOOKUP(MAX(A1),Sheet2!B:B,Sheet2!C:C,""),"")
Answer returned = 19/01/22

=IFERROR(XLOOKUP(A1,Sheet2!B:B,MAX(Sheet2!C:C),""),"")
Answer returned = blank

Sheet1
A1 = 123456

Sheet2
B1 = 123456
B2 = 123456
C1 = 19/01/22
C2 = 23/05/22

As always, I will be super grateful for any help from my favorite community,

JD

 

[Joe4]

Use the MAXIFS function, i.e.

=MAXIFS(Sheet2!C:C,Sheet2!B:B,A1)

 

[deacon10]

Use the MAXIFS function, i.e.

=MAXIFS(Sheet2!C:C,Sheet2!B:B,A1)

Excellent. Is there any way of adapting this to return a blank if nothing has been found?

 

[Joe4]

Try this:

=LET(x,MAXIFS(Sheet12!C:C,Sheet12!B:B,Sheet11!A1),IF(x=0,"",x))

 

[deacon10]

Excellent. Thanks for this.

 

[Joe4]

You are welcome.