Wildcard in Vlook

sbeas

New Member
Joined
Sep 29, 2014
Messages
6
I have a huge spreadsheet of contact. I am trying to scrub out all the email address that contain an email extensions on my exclusion list. For example @gmail.com and I am trying to use a wildcard character to find all the email address within my table that cannot be contacted. I am unable to get them to return a result. I tried the following formula =VLOOKUP([@EMAIL]&"*",'Email Ext'!A2159:A2336,1,FALSE). I am just producing #N/A. When I search on the extension I am finding email address. Due to the size I am hoping to create a formula that will return a message back to "review". I have tried Lookup and cannot get that to work! HELP!

Example of reference list:
Email Extention ListMessage
@gmail.comReview
@yahoo.comReview
@hotmail.comReview

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Gilliam

Active Member
Joined
Jul 10, 2014
Messages
286
use index and match. Match is able to use wildcards while VLOOKUP cannot.
 

sbeas

New Member
Joined
Sep 29, 2014
Messages
6
I tried to index/match and I am still getting a #N/A.

I index the extension exclusion list (separate tab) and then tried to do a wildcard match on the full email address (table). I can't figure out how to find the full email address that contain the extension so I can pull them out of the list.

=INDEX('Email Ext'!A:A,MATCH([@EMAIL]&"*",'Email Ext'!A:A,0))

It seems like the @ sign is causing excel to think it is a formula.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,053
Members
409,800
Latest member
camronmartin

This Week's Hot Topics

Top