Vlookup with wild card

Question

Can someone help me with a question:

Im trying to find the lookup value in column A, in column B.

Column A has multiple values in it, so I have been trying to use a wild card to find it.

any suggestions on what I am doing wrong? I am getting the "NA" error even with cells that only have 1 value?

Answer 1

To use a wildcard on numeric values, you must convert the numeric array to a text array using the TEXT() function.

In the example below, I've used the INDEX-MATCH syntax to find the item:

=INDEX(A:A,MATCH("*" & B1 & "*",TEXT(A:A,"0"),0))

---

Update from Comment

If you have 365 then you could use the MAKEARRAY function and MATCH on that.

I've created three helper LAMBDA expressions - you would save these as a Named Range (Formula tab):

TextItemCount: =LAMBDA(str,sep,LEN(str)-LEN(SUBSTITUTE(str,sep,""))-1)

TextSepPos: =LAMBDA(n,str,sep,FIND("@",SUBSTITUTE(str,sep,"@",n)))

and

TextItemLen: =LAMBDA(n,str,sep,TextSepPos(n+1,str,sep)-TextSepPos(n,str,sep)-1)

The following formula will return the row number of an item in A that is found in B:

=MAKEARRAY(1,TextItemCount(TextToSplit(A2,";"),";"),LAMBDA(r,c,MATCH(TRIM(MID(TextToSplit(A2,";"),TextSepPos(c,TextToSplit(A2,";"),";")+1,TextItemLen(c,TextToSplit(A2,";"),";"))),TEXT($B:$B,"0"),0)))

The end result looks like this: