#NO MATCH error on VLOOKUP using a contact list

I am using the VLOOKUP formula to return an office location (from column 3) in another sheet (named California Partners Data Sheet) using a contact list (named Submitting Lawyer Email). The problem is that the contact list sometimes displays name instead of email and in those instances, my formula returns an error #NO MATCH. How can I get my contact list to only display email addresses, so the formula will work? My formula is below and works for 80% of the entries.

=VLOOKUP([Submitting Lawyer Email]@row,{California Partners Data Sheet Range 4},3,false)

Find more posts tagged with

Accepted answers

All comments

Michelle Choate 2

Hi @Sandra Dye - I have 3 suggestions.

1.) I have stopped using VLOOKUP in favor of INDEX/MATCH. This is a more stable equation as I could add columns anywhere in the source sheet, and the sheet with the formula in it will not change.

2.) Always name your references. So instead of saying {California Partners Data Sheet Range 4} it would be {California Partners Email}. That way when I have multiple ranges from the same sheet in my destination sheet, I know what I am looking at if I come back to it a year later. This is just one of those best practices you have to learn the hard way sometimes.

3.) If you put IFERROR around your formula, it will instead display nothing instead of #NO MATCH. So what this would look like is:

=IFERROR(VLOOKUP([Submitting Lawyer Email]@row,{California Partners Data Sheet Range 4},3,false),"")

If you want to display something else besides a blank, just put it in between the "". So for instance it could be "No Email".

Hope this helps!

Sandra Dye

I don't think you are grasping the problem. It's not the formula or the error message. It's the fact that contact list displays name instead of email. I need the contact list to display an email address, otherwise, regardless of what formula I use, it won't match.

Paul Newcome

You will need a text/number column that has the email addresses in them. You can use a separate sheet as a sort of directory with names listed in one column and emails listed in the other.

Using an INDEX/MATCH will pull the email over based on the name in your contact column. In the event you have an email address in the contact column, you can use an IFERROR around the INDEX/MATCH to simply output whatever is in the contact column.

This will give you a column of nothing but email addresses that you can then use in your VLOOKUP (which I definitely recommend switching out for an INDEX/MATCH formula instead).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.