Partial match with XLOOKUP

[namy77]

Hi guys

I am trying to do an XLOOKUP to do a partial match on keywords using "*" for wildcard search, but this has been resulting in errors. Can anyone point out what I did wrong or give me an alternative solution.

In this example, the lookup cell "Pre-installed aspheric posterior intraocular lens" has the word intraocular as the keyword and should match and return the cell next to it on sheet 2 "implants"

Thanks!

 

[Fluff]

You cannot use xlookup like that, try

=LOOKUP(2,1/(ISNUMBER(SEARCH(Sheet2!$A$2:$A$21,Sheet1!A2))),Sheet2!$B$2:$B$21)

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)