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>
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,603
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top