• 464K Members
• 11.4K Online
• 560K Conversations

## Searching and matching phone numbers in a list against spreadsheet data.

Occasional Contributor

# 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 so. and on another tab I have post codes. abnout 50...

What I want/need to do is to identify the cells in the data that have a match against the list of phone numbers and then to id the cells that match against the post code information.

I thought Vlookup would do it, but that seems to only look for exact match, but I am only wanting to ID the cells with the matching numbers in it so it wont give me a result. Have tried SEARCH function also but not making any progress.
any help is gratefully accepted! Cheers

10 Replies

# Re: Searching and matching phone numbers in a list against spreadsheet data.

You can use VLOOKUP or INDEX-MATCH with the wildcard character (*) in the lookup_value argument.

# Re: Searching and matching phone numbers in a list against spreadsheet data.

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.

# Re: Searching and matching phone numbers in a list against spreadsheet data.

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

# Re: Searching and matching phone numbers in a list against spreadsheet data.

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.

# Re: Searching and matching phone numbers in a list against spreadsheet data.

You can use COUNTIF with wildcard to count the instances of the lookup_value, then use the result as the array argument of INDEX.

# Re: Searching and matching phone numbers in a list against spreadsheet data.

dont suppose someone would have an example if this is my original equation>
=VLOOKUP("*"&A2&"*",'Data File'!A:B,2,FALSE)

# Re: Searching and matching phone numbers in a list against spreadsheet data.

Try this formula in B2:
=LOOKUP(PI(),
1/(‘Data File’!A:A=A2),
‘Data File’!B:B)

# Re: Searching and matching phone numbers in a list against spreadsheet data.

Let me correct myself:
B2=LOOKUP(PI(),
1/(ISNUMBER(FIND(A2,’Data File’!A:A))),
‘Data File’!B:B)

# Re: Searching and matching phone numbers in a list against spreadsheet data.

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!

# Re: Searching and matching phone numbers in a list against spreadsheet data.

If your data has duplicates, the formula returns the last match.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies