Alternative to vlookup with exact and approximate

问题:

Cell A1: 0553400710 
Cell A2: John
Cell B1: ['0553400710', '0553439406']

Note:

1. List item Cell B1 has a fixed format of ['number','number,'number',...... ]
2. A1 and A2 are user input values

I want to match 0553400710 in Cell A1 with ['0553400710', '0553439406'] in Cell B1.

If it matches, I want to return A2: John.

Is it possible?

Vlookup failed to work by the way. I am looking for some technique which uses the advantage of fixed format

Picture 1: This is the formula i have tried

Picture 2: This is the table where the vlookup is showing wrong values

Picture 3: This is the array where vlookup check

回答1:

Going by the sample data (and references) in your narrative and ignoring the image(s), a simple wildcard match should be sufficient.

=IFERROR(INDEX(A:A, MATCH("*"&A1&"*",B:B, 0)+1), "")

回答2:

Have you tried index/match?

=index(return_range,match(cell_to_match,range_to_match,0))