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.
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.
Best Answer
-
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)), "")
Gia Thinh Technology - Smartsheet Solution Partner.
Answers
-
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)), "")
Gia Thinh Technology - Smartsheet Solution Partner.
-
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.
-
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
Gia Thinh Technology - Smartsheet Solution Partner.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!