VLOOKUP on column with letters and numbers results in #NOMATCH

Options

I have a simple VLOOKUP to pull back the related column from a 2 column table. It consists of either letters only (WORD) or numbers only (123) for each entry but entries span both types. When the function is in place I receive a #NOMATCH error for all the letters only entries. I've tried adding a helper column that refers to the initial column (=VALUE(column_name@row + "") as well as =column@row) but neither seem to do the trick. Looking through existing entries seems hit or miss for success. Any news on this front?

Tags:

Best Answers

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    You need to add that column to BOTH sheets and ditch the VALUE function.


    =[Column Name]@row + ""


    This converts everything into text values. Doing this on both sheets and then referencing these two columns should clear things up.

  • jonnyp
    jonnyp ✭✭
    Options

    Hi @Paul Newcome. The current formula is below. As you can see it includes a cross reference to another sheet. Are you saying that I'll need a helper column in both the source and the destination sheets? i.e. a helper for the "Docs" column in the destination sheet and a helper for the lookup column in the {Global Doc Type} cross reference source sheet? Not fully understanding what you're saying. Appreciate the guidance!

    =VLOOKUP(Docs@row, {Global Doc Type}, 2)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Yes. Whatever column you are matching on. You will need a helper column in both sheets that converts the entry in every row into a text value so that all data types are the same.

  • jonnyp
    jonnyp ✭✭
    Options

    Still running into a #NO MATCH on everything now. Maybe you can help out. Here's what I've done.

    For the destination sheet, I've created a helper column for "Docs" called "Docs Helper"

    =Docs@row + ""

    For the source sheet, I've created a helper column for "Document" called "Document Helper"

    =Document@row + ""

    I've created a column formula on the destination sheet

    =VLOOKUP([Docs Helper]@row, {Global Doc Type}, 2)

    where {Global Doc Type} is a cross reference sheet that contains two columns: "Document Helper" and the column to match to.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    I honestly don't like working with VLOOKUP. Let's try switching over to an INDEX/MATCH.


    =INDEX({Column To Pull From}, MATCH([Docs Helper]@row, {Source Sheet Doc Helper}, 0))

  • jonnyp
    jonnyp ✭✭
    Options

    I think we're close! I've updated the formula in the "Direction" column of my destination sheet but I'm getting spotty results. Not sure why.

    Here's what I have.

    =INDEX({Direction}, MATCH([Docs Helper]@row, {Global Doc Type}, 0))

    Where...

    {Direction} is the cross reference column that contains the direction (inbound or outbound) in the source sheet.

    [Docs Helper] is the helper column for [Docs] in the destination sheet.

    {Global Doc Type} is the cross reference lookup that contains two columns: [Document Helper] and the column to match to, [Direction] in the source sheet.

    Attaching some screenshots as well to help illustrate.

    Destination Sheet with formula 'working' mostly for numerical entries

    Destination sheet again with formula not working for text entries.

    Source sheet with lookup info


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    The first cross sheet reference should be the direction column only, and the second cross sheet reference should be the doc helper column only.

  • jonnyp
    jonnyp ✭✭
    Options

    Yes! This did it. This was driving me crazy. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!