How do I use XLOOKUP for multiple criteria?

Question

My question is simple enough. It involves, using XLOOKUP formula. So, following is the issue, I'm having.

• I have master data sheet, which I'm using for the lookup reference.
• In sheet XLOOKUP test, I'm looking up the Task Completion Status for the search keys Sl No. in cell C3 and Date in cell C4.

I've applied the formula-

XLOOKUP($C$3 & $C$4, 'Master Data'!$A$2:$A$9 & 'Master Data'!$C$2:$C$9, 'Master Data'!$D$2:$D$9, , 0, 1)

But, it throws an #N/A error message with Array arguments to XLOOKUP are of different size..

What am I doing wrong here?

Here's the link to a demo sheet, if needed.

Answer 1

It is usually easier to use filter(), like this:

=filter('Master Data'!D2:D9, C3 = 'Master Data'!A2:A9, C4 = 'Master Data'!C2:C9)

To answer the question, to do the same with xlookup(), you can combine columns into a compound key, as attempted by the formula in the question. To combine columns of multiple rows with the & operator, you need to wrap the expression in arrayformula(), like this:

=xlookup( 
  C3 & "→" & C4, 
  arrayformula('Master Data'!A2:A9 & "→" & 'Master Data'!C2:C9), 
  'Master Data'!D2:D9 
)

See your sample spreadsheet.