I am using the VLOOKUP function, but getting #NOMATCH?

I am using the VLOOKUP function to reference another sheet and pull in data. I have it in another area of my sheet pulling different data from a different sheet and it works fine, but this particular one keeps returning #NOMATCH. The screenshots below show the formula and field to lookup, along with the reference sheet and the columns in the reference.

Formula: =VLOOKUP(Date@row, {2021 MENF Newsletter Range 1}, 3, false)

Any ideas on what I'm doing wrong or how I should format something to make sure it pulls the data?

Find more posts tagged with

Formulas

Accepted answers

Debbie Sawyer

Are you certain that both your date columns are formatted as date columns?

Ie. if your Ad Date column from your lookup range is a Text/Number type then it wouldn't match the date from the first sheet against it.

This is where I'd check first.

Kind regards

Debbie

All comments

Debbie Sawyer

Are you certain that both your date columns are formatted as date columns?

Ie. if your Ad Date column from your lookup range is a Text/Number type then it wouldn't match the date from the first sheet against it.

This is where I'd check first.

Kind regards

Debbie

Megan Yaussi

What a silly thing to miss. Thank you for the help! I hadn't realized that could make or break a date formula like that.

SS_newbie_CF

I'm having the same problem, getting a #NOMATCH returned. Here's my equation: =VLOOKUP([Customer Name]@row, {Customer Profile Range 1}, 17). The search_value is the first column of the spreadsheet and it is a Text/Number field. The lookup_table is a linked reference to another sheet with the first column being the Primary Column and the one I'm searching. Is it because the lookup is a Primary Column? it's very weird because some of the lookups return a value but others return the #NOMATCH. I've copied and pasted the values to make sure they are exact. I've also played with the true/false setting and that changes the returned results switching some that were found to no match while others that were no match return the value.

Debbie Sawyer

@SS_newbie_CF

The quickest way of getting you a solution is to meet via zoom or MS Teams and you can show me the sheets in question and we can determine why you are getting a #NoMatch error.

I am more than happy to do this.

Also, I would recommend changing your Vlookup function to an Index(Match()) function. The vlookup function uses a lot more cell links than an Index(Match()) - each sheet has cell link limitations, the vlookup also relies on no-one moving your column's around! (adding columns, deleting other columns and moving columns will break a vlookup!))

Please do email me debbie.sawyer@smarterbusinessprocesses.com if you'd like me to work your formula through with you and show you how to convert it to a more robust Index one :D

Kind regards

Debbie

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.