use xlookup with wild cards

[still learning]

Hi

I’m trying to use Xlookup for a partial match
A1 is the reference cell
A2:A4 has first and last name of a person
B2:B4 has dates
I keep getting “no”
I’ve tried to use

=XLOOKUP(A1,A2:A4,B2:B4,”no”)

AND

=XLOOKUP(A1&”*”,A2:A4,B2:B4,”no”)

AND

=Xlookup(“*”&A1”*”,A2:A4,B2:B4,”no”)

If I use both names in A1, and not use the wild cards, I get the correct results

mike

 

[Cubist]

Maybe try this. It returns all dates with partial match.

Book1
	A	B	C	D	E
1	Apple			1/6/2021
2	Apple Bee	1/6/2021		2/9/2021
3	Bee Ghee	1/23/2021		3/15/2021
4	Apple Macbook	2/9/2021
5	Ghee	2/26/2021
6	The Greatest Apple Tree	3/15/2021
7
Sheet6

Cell Formulas
Range	Formula
D1:D3	D1	=FILTER(B2:B6,ISNUMBER(SEARCH(A1,A2:A6)),"no")
Dynamic array formulas.

 

[Fluff]

How about

=XLOOKUP("*"&A1&"*",A2:A4,B2:B4,"no",2)