Bug in VLOOKUP on a RANKEQ formula?

Hello,

I'm using VLOOKUP to find rank order from another sheet which uses RANKEQ. Periodically (pretty frequently, like every 24-48hr) the VLOOKUP will return an error. Referencing the RANKEQ on the other sheet everything is in order, the VLOOKUP just fails to find the specified rank order (i.e., 1, 2, etc.).

What is strange is I've found a fix: When the VLOOKUP fails, if I overwrite the RANKEQ formula with a number (i.e., 1), save and then return to the VLOOKUP, it repopulates with the correct data. I then return to the RANKEQ and replace the number with the RANKEQ formula again and all is well. Seems like a Smartsheet bug, but want to check here first.

Example of my formulas:

=VLOOKUP(Number, {Second Sheet RANKEQ Formula Range 1}, Return String, 0)

=IF(A@row <= (First Sheet Range 1} + {First Sheet Range 2}), RANKEQ(A@row, A$1:A$13, 1) + COUNTIF(A$1:A@row, IFERROR(RANKEQ(@cell, A$1:A$13, 1), 0) = RANKEQ(A@row, A$1:A$13, 1)) - 1, "")

Thanks!

Find more posts tagged with

Accepted answers

All comments

Jaykel Torres

Hey @William Doolan,

I ran a few tests and can confirm there is nothing incorrect with the formulas used. As this does happen intermittently, I do recommend reaching out to our Technical Support Team here: https://help.smartsheet.com/contact/smartsheetapp for further assistance.

Instead of using the VLOOKUP Function, try using an INDEX/MATCH Formula to see if the error occurs again.

I am unsure which formula error you may be receiving but feel free to review our Help Article: Formula Error Messages to isolate the issue.

I hope this helps!

Jaykel

William Doolan

Thanks Jaykel. I am not receiving any formula errors, which tipped me to this being a bug. The formulas are robust and behave properly except the VLOOKUP falls asleep and stops looking up what it is searching...the overwrite with any number wakes it up 100% of the time and it stays awake for about 24hrs after the original RANKEQ formula is rewritten.

I'll shotgun it and get the support team on it and give the INDEX/MATCH a shot!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.