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

• 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 pops out an #N/A error message with Array arguments to XLOOKUP are of different size..

So what am I doing wrong ?

Here's a link to my sheet, if needed.

CodePudding user response：

To combine columns into a compound key, you need to wrap the formula in arrayformula(), like this:

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

Alternatively, use filter():

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

See your sample spreadsheet.