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

Awesomas

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.

Thanks

Awesomas

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.

Andrée Starå

Hi @Awesomas

I hope you're well and safe!

There isn't a Lookup function in Smartsheet, but a VLOOKUP.

https://help.smartsheet.com/function/vlookup

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kelly Moore

Hey @Awesomas

Try this lookup formula instead. The INDEX/MATCH is a robust lookup combo in smartsheet

=INDEX({Sheet-SLA Definitions SLA Value}, MATCH(Priority@row, {Sheet-SLA Definitions SLA Name},0))

Note that each range is a single column. You will need to physically create each cross reference range - you can't simply copy paste.

Kelly

Kelly Moore

oops sorry @Andrée Starå - we were answering at the same time.

Kelly

Andrée Starå

@Kelly Moore

No worries! Also, you had a different answer than me, so it adds value.

Awesomas

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.