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

Options

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?

Tags:

Best Answer

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • Debbie Sawyer
    Debbie Sawyer ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Megan Yaussi ✭✭✭✭
    Options

    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
    Options

    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
    Debbie Sawyer ✭✭✭✭✭✭
    Options

    @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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!