VLOOKUP Output Doesn't Match Expected Value

Adam D.

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.

Formula to determine row number

Fred Meissner

Dec 13, 2016

Hello,

 

I am wondering if there is a way to determine the row number of a specific row.  I know this can be done in Excel with the row() function.  This does not work in Smartsheet.  The reason I am looking for it is because I have a formula that needs to apply to every row that checks the row above it, but that fails when it is in the first row.  I can't just have a static first row because the entries move around based on priority.

 

If there is a way to determine the row, the formula can check if it is in the first row, and then act accordingly.

 

Thanks,

Fred

---

View Post

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.

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)), "")

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