Xlookup range formula

Question

I got an issue with Xlookup formula for the lookup array & return array. As the 1st one I need to manually select the range for lookup and return array, I try to use Xlookup with multiple criteria to match with the keyword instead of only matching one criteria. However, the result was appear to be different. Please help how to adjust the formula to remove the manual selecting range. Thank you.

• G column is text
• AE column is number
• J column is text
• Z column is number
• W column is date

=XLOOKUP(AE3,工作表2!$Z$2:$Z$6,工作表2!$W$2:$W$6,,1)

=XLOOKUP($G3&$AE3,工作表2!$J:$J&工作表2!$Z:$Z,工作表2!$W:$W,,1)

Answer 1

Your sheet names showing in Chinese may be confusing some people.

I'm not aware of any syntax in Excel that allows you to concatenate columns as part of an array definition. It would be great if we could do this. The only way I know to do this is with another column and then use that for your array. You could add $J2&$Z2 to another column and then use that. If you added that to column H your xlookup() would be:

=xlookup($G3&$AE3, $H2:$H6, $W2:$W6,,1)

Here's how it looks. I showed the xlookup() functions in F9 and F10 (right aligned) and the results in G9 and G10.