|
Wild Cards in Vlookup
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 |
Wild Cards in Vlookup
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Hardeep Kanwar" wrote: 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 |
Wild Cards in Vlookup
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 "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Hardeep Kanwar" wrote: 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 |
Wild Cards in Vlookup
Hi Hardeep,
=VLOOKUP("*"&1204629893&"*",A1:A100,1,FALSE) produces: 9810332270/1204629893 from: 9810332270/1204629893 -- If this post helps click Yes --------------- Peggy Shepard "Hardeep Kanwar" wrote: 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 "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Hardeep Kanwar" wrote: 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 |
| All times are GMT +1. The time now is 09:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com