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.

Thanks

Find more posts tagged with

Sheets

Formulas

Accepted answers

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.

All comments

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.