Lookup() returning #NO MATCH when it is definitely there

I have created a new sheet to store the priority values of my ticketing system so if I change the values I don't need to change all the formulas on my main sheet. It's used to determine when to set the due date. It works fine for Low and normal, when I change the values it changes the due date correctly, but when I select "High" it says #NO MATCH. I have checked that there is no spaces at that they are both identical. I just can't see where I'm going wrong. Here's some screenshots

Any ideas would be really helpful.


Best Answer

  • Awesomas
    Answer ✓

    Thank you @Andrée Starå and @Kelly Moore for you answers. @Kelly Moore I will keep your solution in mind for another problem, thanks. I found the Lookup() function on the Smartsheet formula examples (not sure who created this). It is a good reference but probably didn't serve me well this time. Is this a legacy formula that was replaced by VLOOKUP()? I used this example because I thought I didn't need to tell it to (exact) match false/true.

    In the end, all I needed to do was put false in at the end of the formula (strange) to force an exact match. Both versions below worked.

    "=VLOOKUP(Priority@row, {SLA Def}, 2, false) + Created@row"

    "=LOOKUP(Priority@row, {SLA Def}, 2, false) + Created@row"

    I have replaced my formulas to be the VLOOKUP version

    Again Thank you both for your help.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!