VLOOKUP Output Doesn't Match Expected Value

Hey Everyone,

I've been having a hard time with some missing features or current limitations within SmartSheet but have found the communities helpfulness in finding various work arounds very useful. I'm hoping that somebody will have an idea for the issue i'm currently running into now.

Having made some helper columns which can auto update to reflect the row number thanks to another post (link below) I'm trying to look up the value of another column from the predecessor row of a task.

https://community.smartsheet.com/discussion/6757/formula-to-determine-row-number

Using the VLOOKUP formulas below I'm not getting any usable data out of it.

=VLOOKUP(Predecessors350, [test2]:[Task Name], 2) ——> #NO MATCH

=VLOOKUP(Predecessors@row, [test2]:[Task Name], 2) ——> #NO MATCH

What I find particularly odd is that if I validate the data with the formulas below (COUNTIF, COUNTIFS & IF) I can determine that using the predecessor column works as a "search" criteria. I've tried it with specifying the cell as well as keeping it generic to the current row but everything works as expected in these tests.

=COUNTIF([test2]:[test2], Predecessors350) ——> 1

=COUNTIFS([test2]:[Task Name], Predecessors@row) ——> 1

=IF([test2]349 = Predecessors350, "SAME", "NOPE") ——> SAME

I'm trying to figure out why I'm not getting an output of "DOOR 2" in this example using my VLOOKUP formula as I'd like to use this approach for determining if there are any overlapping / matching dates to adjust an item count for a report I'm working on.

Any ideas or suggestions would be appreciated and if you need more info please just let me know.

Thanks,

Adam D.

Find more posts tagged with

Formulas

Accepted answers

Gia Thinh

Hi Adam D.,

In your use case, it is better to use INDEX/MATCH functions instead of VLOOKUP. Try this column formula in the Door? column as the screenshot below. Hope it works for you.

=IFERROR(INDEX([Task Name]:[Task Name], MATCH(VALUE(Predecessors@row + ""), [test 2]:[test 2], 0)), "")

All comments

Gia Thinh

Hi Adam D.,

In your use case, it is better to use INDEX/MATCH functions instead of VLOOKUP. Try this column formula in the Door? column as the screenshot below. Hope it works for you.

=IFERROR(INDEX([Task Name]:[Task Name], MATCH(VALUE(Predecessors@row + ""), [test 2]:[test 2], 0)), "")

Adam D.

Thanks @Gia Thinh,

That definitely worked instead of the VLOOKUP though it may not be as intuitive to come up with. One quick follow up question regarding your formula. Why the need to change the Predecessor value to "VALUE" as well as the need to add a null value to the end of the?

Is the Predecessor column not already a numeric value?

Again thanks for the help, hopefully I can now extrapolate this into my whole sheet and get what I need out of our report.

Take care,

Adam D.

Gia Thinh

The Predecessor value is not a numeric value, and not actually a string value. At the beginning, I used VALUE(Predecessors@row) but it did not work, so I added a null value to make it a tring, then change it back to a value with VALUE(Predecessors@row + "")

Im happy it worked for you

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.