Forum Discussion
Searching and matching phone numbers in a list against spreadsheet data.
Hi, I have some data (3000 lines) which contains a lot of text within cells and within that data are phone numbers and post codes etc. On another tab I have a long list of phone numbers, about 150 or...
dont suppose someone would have an example if this is my original equation>
=VLOOKUP("*"&A2&"*",'Data File'!A:B,2,FALSE)
thanks in advance...
Try this formula in B2:
=LOOKUP(PI(),
1/(‘Data File’!A:A=A2),
‘Data File’!B:B)
=LOOKUP(PI(),
1/(‘Data File’!A:A=A2),
‘Data File’!B:B)
- If your data has duplicates, the formula returns the last match.
- Thanks again Twifoo, looks like its almost working for me! although i am now wondering what to do if the number occurs more than once in my Data.... thanks again!
- Let me correct myself:
B2=LOOKUP(PI(),
1/(ISNUMBER(FIND(A2,’Data File’!A:A))),
‘Data File’!B:B)