Wild Cards in Vlookup

[Hardeep Kanwar]

Hi! Experts

I have Phone number in 2 columns

Column A

Phone Number
9548878570/1204629955
9307584305
9690143335
9336884522
9368888865
9236644465
9250136689
9718856209
9278144529
1204629813
1204629955
1204211957
1204629893
9456366784

Column E

Phone Number
9336884522
9212544153/9368888865/9212572476
9236644465
9250136689
9718856209
9278144529
9810332270/1204629813
1204629955

Its only a Example Actually Data is Around 4000

You see in Both Columns there are Phone Numbers some has single Number but
some has Multiple Phone Numbers in a Single Cell.

For Ex.
In column A 1204629893
but in column E 9810332270/1204629893

When i Insert Vlookup or Index/Match Function it show #N/A

is there any way to using wild Card in Vlookup fuction. to match this Phone
Numbers

Thanks in Advance

Hardeep Kanwar

 

[Shane Devenshire]

The solution will depend on data type in the cells. Are the phone numbers
stored as text or numbers? Certainly the double phone numbers are text but
what about the others?

=VLOOKUP("*"&TEXT(C1,"@")&"*",TEXT(A1:A14,"@"),1,)

This will work with for both text and/or numbers but there is one unusual
trick, you must enter it as an array - press Shift+Ctrl+Enter to enter it,
not Enter. In this example, I have check the entry in C1 with the entries in
column A, you should adjust according to your needs.

 

[Hardeep Kanwar]

Thanks for Reply

Your Formula Works but not Completely

In mine Example, its not working in 2 Cells

In column E
9212544153/9368888865/9212572476
9810332270/1204629813

Number 9368888868 and 1204629813 both Exists in Column A

In column A
9368888865
1204629813

Thanks in Advance

Hardeep Kanwar

 

[pshepard]

Hi Hardeep,

=VLOOKUP("*"&1204629893&"*",A1:A100,1,FALSE)

produces:

9810332270/1204629893

from:

9810332270/1204629893