Problem with Xlookup's Wildcard search

[Eric Tsang]

Dear all

I come across to a problem on the formula of Xlookup when i use the Wildcard syntax

=XLOOKUP("*"&H8&"*",C7:C9,B7:B9,XLOOKUP("*"&H8&"*",D7:D9,B7:B9),2,1)

See the attached.

The formula works well in normal situation, without the wildcard search

Can tell me where went wrong?

appreciated

Eric

[Sam Capricci]

First, I don't have xlookup available in my version of excel, but if you are trying to look up the product AND the Shop 1 name that matches H8, a couple things.
if what you are looking to return is "Major John" then your xlookup should probably look something like this...
=XLOOKUP("*"&H8&"*",C7:C9,B7:B9)&" "&XLOOKUP("*"&H8&"*",D7:D9,B7:B9)
Secondly, your wildcard isn't necessary because the value in H8 exactly matches the value in D9 -- though I suspect you are just testing it out.
Here would be the correct syntax (and working formula) if using an index match to return "Major John"...
=INDEX(B7:B9,MATCH("*"&H8&"*",D7:D9,0))&" "&INDEX(C7:C9,MATCH("*"&H8&"*",D7:D9,0))
and it works with the wild card or without it. Maybe you can adapt your xlookup formula to suit.

[Eric Tsang]

Dear Sam

Thank you for your reply

Perhaps i did not make it clear.

I have redo it for your help

My aim is to find out where the manager is based.

I want to use the wildcard with Xlookup, is in case, the manager provides the full name, I still can find them.

Hope i have made it clear this time

Eric

[wk9128]

Cell G8 formula

Please Login or Register  to view this content.

[HansDouwe]

The outer XLOOKUP (in workbook post #3) has not a match_mode and search_mode specified.

Please try

Please Login or Register  to view this content.