Alternative to vlookup with exact and approximate match doesnt work

2
votes
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

Formula

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

enter image description here

Picture 3: This is the array where vlookup check

enter image description here

2
excelexcel-formulavlookupstring-matchingvba
Welcome to SO! Would you please edit your question to include what formulas you already tried, and what results you had? A screenshot of your spreadsheet would also be helpful in visualizing the problem. Thank you! – cxw
@Joanne you can use Instr function to check if the sub-string in Cell A1 is found at Cell B1. – Shai Rado
I have updated the pictures – Joanne

2 Answers

2
votes

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), "")

wildcard_match

0
votes

Have you tried index/match?

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