Why isn't my VLOOKUP Formula returning a match?

I've input a column formula to cross reference another sheet, and return the value in the 3rd column when there's a match. The formula is =VLOOKUP(Email@row, {Table with 3 data columns in the other sheet},3)

The formula worked for the first row of data, but the second returns "#NO MATCH. I'm really confused at to why this isn't working. I've tried to convert the column on the reference sheet to contact, text, and I've even tried linking the cells in the reference sheet to the column containing the search value in the VLOOKUP (Email column).

Any advice would be appreciated


Best Answer

  • JCluff
    JCluff ✭✭✭✭
    edited 02/28/23 Answer ✓

    @SIFF_Volunteers,

    Without seeing how your reference table is set-up I'm not sure if we can fully diagnose the VLOOKUP error. I would actually recommend that you start using INDEX(MATCH()) to avoid errors when columns/data are moved. The formula would look like:

    =INDEX({Code Column},MATCH(Email@row,{Recipient Column},0))

    If you've never used INDEX(MATCH() formulas, I'd definitely recommend doing some quick research online. There are a ton of resources out there and it will make a huge difference in your sheet formulas.

Answers

  • Update, I solved this with an INDEX/MATCH function instead. In the column where I want to return the value:

    =INDEX({Column on Reference Sheet with Code},MATCH(Email@row,{Column on reference sheet with emails},0))

  • JCluff
    JCluff ✭✭✭✭
    edited 02/28/23 Answer ✓

    @SIFF_Volunteers,

    Without seeing how your reference table is set-up I'm not sure if we can fully diagnose the VLOOKUP error. I would actually recommend that you start using INDEX(MATCH()) to avoid errors when columns/data are moved. The formula would look like:

    =INDEX({Code Column},MATCH(Email@row,{Recipient Column},0))

    If you've never used INDEX(MATCH() formulas, I'd definitely recommend doing some quick research online. There are a ton of resources out there and it will make a huge difference in your sheet formulas.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!