XLookUp with multiple Criteria

BittenApple
BronzeLounger
Posts: 1506
Joined: 01 Mar 2015, 02:03

XLookUp with multiple Criteria

Post by BittenApple »

Hello Hans,

Do we need to use multiple criteria in XLookUp to prevent the function not to return the first matched Lookup value as VLookUp does?


Second question:

Which one is better approach for XLookUp with multiple criteria?

=XLOOKUP(B18&C18,$B$5:$B$15&$C$5:$C$15,$D$5:$D$15)

or

=XLOOKUP(1,($B$5:$B$15=B18)*($C$5:$C$15=C18),$D$5:$D$15)

Please advise me.

Regards,

BittenApple :thankyou: :scratch: :thankyou:

User avatar
HansV
Administrator
Posts: 80088
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: XLookUp with multiple Criteria

Post by HansV »

1) Just like VLOOKUP, XLOOKUP returns the value corresponding to the first match it finds.
The difference is that in VLOOKUP, you must specify FALSE as fourth argument if you want an exact match. XLOOKUP looks for an exact match by default; if you want something else you have to specify it.

2) Both versions have the same result, just use the one you like best, or find easiest to write.
Best wishes,
Hans