Wildcard in Vlook

[sbeas]

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 List	Message
@gmail.com	Review
@yahoo.com	Review
@hotmail.com	Review

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

 

[Gilliam]

use index and match. Match is able to use wildcards while VLOOKUP cannot.

 

[sbeas]

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.