Forum Discussion

Copper Contributor

Feb 26, 2019

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...

excel

Formulas and Functions

K_ONE_ILL

Copper Contributor

Feb 26, 2019

Hi Twifoo, thanks for the comment, I have attempted it now with wild card, but it gives error in equation, does this look like it should work?
=VLOOKUP(*A2*,'Data File'!A:B,2,)

thanks.

Twifoo

Silver Contributor

Feb 27, 2019

Your formula should return your expected result unless your lookup_value is not available, wherein it returns #N/A error.

K_ONE_ILL
Copper Contributor
Feb 27, 2019
Hi Twifoo et al,
the equation does actually work, the thing that is limiting it is that the vlookup seems to have a limit of only the first 255 characters in the cell..... is this an actual thing? seems crazy to me! and super annoying that the error makes no mention that this is why......
anyone out there know if there is a way of overiding the 255 max limit?
Thanks.
- Twifoo
  Silver Contributor
  Feb 27, 2019
  You can use COUNTIF with wildcard to count the instances of the lookup_value, then use the result as the array argument of INDEX.
  - K_ONE_ILL
    Copper Contributor
    Feb 27, 2019
    dont suppose someone would have an example if this is my original equation>
    =VLOOKUP("*"&A2&"*",'Data File'!A:B,2,FALSE)
    
    thanks in advance...