How to pass Row Number to MATCH function?

Dave Robinson

Hello all!



I am trying to work with the MATCH and VLOOKUP functions, to create alerts to let people know it's time to begin their assigned work.

For example, if Task 2 is a predecessor of Task 3 (that is, if [Predecessors]3 = 2), I want to do a MATCH and VLOOKUP on Row 2 that shows [Assigned To]3. That way, when [Status]2 = Complete, I can notify [NEXT_IN_LINE]2 (which is really [Assigned To]3).

My problem is that I'm getting "#NO MATCH" errors when I try to pass in the value of a predecessor to the MATCH function. The MATCH function seems to fail if I don't pass it a string, even if I wrap it in (escaped) quotes.

This works:

• MATCH("2", Predecessor:Predecessor, 0)

(Note that I am directly entering the row number as a string, in quotes. This is not the result of a formula, this is manual.)

These do not work:

• MATCH(ROW_ID@row, Predecessor:Predecessor, 0)
• MATCH(VALUE(ROW_ID@row), Predecessor:Predecessor, 0)
• MATCH("\"" + ROW_ID@row + "\"", Predecessor:Predecessor, 0)
• MATCH("\"" + VALUE(ROW_ID@row) + "\"", Predecessor:Predecessor, 0)
• Defining a new column called [PredString] which = "\"" + Predecessor@row + "\"", and then doing MATCH([PredString]@row, Predecessor:Predecessor, 0)

I don't want to have to manually populate a column full of MATCH formulas each with the row number in quotes so that the VLOOKUP will work. This seems way too time-consuming to generate, and tough to train other users on; also, it won't be durable in case of rows being added, etc.

Also, I am aware that even if this method ends up working, the MATCH formula is only going to find the first result - so this won't be good for cases where a single task is a predecessor for multiple tasks (or where one task has multiple predecessors). That's fine.

We appreciate any help you can offer! Thank you and take care!

Paul Newcome

In your PredString column, you can just use a basic

 

=Predecessors@row

 

to turn the predecessor into a text string that can be used in formulas. However if you have multiple Predecessors, you will need to parse them out and reference each cell (much easier than trying to parse it out in the formula using a bunch of MID functions).

Dave Robinson

Hey Paul, thank you for looking into this! I must apologize, I think I've led you down the wrong path. I didn't end up needing to define a PredString column at all.

What I need is to be able to use the MATCH function to take the number of the current row (call it Row X) and find the first row (Row Y) where [Predecessor]Y = [Row_ID]X.



If I have a task on row 3 that needs to be done before the task in row 4, then the formula should work like this:



MATCH("3",Predecessors:Predecessors,0). // Returns "4"



The search term in my MATCH function is the current row number, not the current row's predecessor value. I haven't been able to pass the current row number into the MATCH function without explicitly typing out the current row # within quotes.



All the ways that I've tried to pass the current row # to the MATCH function are yielding a "#NO MATCH" error. (Again, the lone exception to this is when I explicitly type the current row # within quotes, e.g., "3")

Paul Newcome

Ah. Ok. You would need a way of producing the row number in an additional column. You can then reference that column in your MATCH function. Unfortunately setting up a way to ID a row that will hold up to sorting, adding, and deleting can get a little tricky.

 

If you are not worried about those issues, you could use a basic Auto-Number type column.

 

What exactly is it that you are trying to use the MATCH function for? What's the overall plan for all of this? There may be another workaround.

Dave Robinson

Thanks again! I'm ultimately trying to set up a column called "Next In Line", whose value is the person Assigned To the next task in the dependency chain. This is so I can set up a notification rule: email [Next In Line]@row when Status@row = "Complete".

My MATCH() formula is supposed to take the current Row # as an input, look in the Predecessors column, and return the row of the first task that is a "successor" of the current row/task. We then pass this into a VLOOKUP (or INDEX) to get the "Assigned To" value from the same row as the successor task.

I've been exploring options on how to work with predecessors, how to alert the next person in line, etc. There's another post on the Smartsheet forums about setting up a column/formula to track Row #: https://community.smartsheet.com/discussion/formula-determine-row-number.



Unfortunately, for whatever reason, I am not able to plug [ROW_ID]@row into the MATCH() function. This means that the MATCH returns "#NO MATCH" and the VLOOKUP fails.

Again, if I explicitly enter, say, "3" into the MATCH() function, I get a valid answer. However, I can't use [ROW_ID]3, [ROW_ID]@row, or escaped versions of these (e.g., "\"" + [ROW_ID]@row + "\"") within the MATCH function - I still get the "#NO MATCH" error. Like I said, I've tried to use VALUE(), I've tried to wrap it in quotes, I've done both of those at the same time... I just can't figure out why MATCH() doesn't want to accept the ROW_ID value, in whatever form, as an input!

Dave Robinson

Thanks again. I tried to reply earlier today, but for some reason, the post never went through? Anyway...

I'm ultimately trying to let the "next person in line" know that their work is ready to begin. I want to be able to define [Successor]@row and [Next In Line]@row so that when Status@row = "Complete", we send a notification to [Next In Line]@row.

Someone posted a formula that gives the current row number, in an older thread here: https://community.smartsheet.com/discussion/formula-determine-row-number

I'm using that technique, where we define ROW_ID for every row. That should let me pass in the current row # (as "ROW_ID@row") to the MATCH function, but MATCH keeps giving me "#NO MATCH" errors when it shouldn't.

I ultimately want to define:

Successor@row = MATCH(ROW_ID@row, Predecessors:Predecessors, 0)

and

[Next In Line]@row = VLOOKUP(Successor@row, Predecessors:[Assigned To], #_of_columns_between_predecessors_and_assignedto).

Again, all this is in the service of dynamically generating the "successor" to any given task, and letting the person assigned to the "successor" task know that the predecessor task has been completed.

The technical issue here is that MATCH() doesn't want to accept ROW_ID@row (or string versions of the same thing, like "\"" + ROW_ID@row + "\"") in the formula. If I manually enter something like MATCH("8", Predecessors:Predecessors, 0), it will give the correct answer. However, even if my ROW_ID@row = 8 (or the string "8"), the MATCH function fails and only gives "#NO MATCH".

Paul Newcome

Great find on the row numbering issue. I wish I had seen that before.

 

For the NO MATCH error... I don't know why I didn't think of this before...

 

The "number" you are pulling for your MATCH function is probably actually a text string representing a number (very common when determined by a formula). Try wrapping it in a VALUE function.

 

=MATCH(VALUE([Row_ID]@row), Predecessors:Predecessors, 0)

 

The reason it won't find it is because the VLOOKUP is looking for a text string representing a number, and the cell that the result should be coming from is an actual number (or the other way around). You could also wrap your Row ID formula in a value function

 

=VALUE(Formula that determines Row ID)

.

EDIT:

 

After re-reading your last comment a few times, something finally registered. Your row numbers in the predecessor column are text string.

Add an additional column and use

=VALUE(Predecessors@row)

Use THAT column as your range in the MATCH function.

.

RE-EDIT: That produces an invalid data error... Hmm.....

.

Re-RE-Edit:

Helper Column:

=Predecessors@row

PredHelp Column

=VALUE(Helper@row)

.

Reference PredHelp@row in your MATCH function.

.

Additionally, I would suggest an INDEX/MATCH instead of a VLOOKUP. It is much more flexible and less likely to break. Something along the lines of 

 

=IF(INDEX(Status:Status, MATCH(PredHelp@row, [Row_ID]:[Row_ID], 0)) = "Complete", "Ready to start")

.

This will look at the value in the PredHelp column, find it in the Row_ID column then pull the value from the Status column from that row. If that value is Complete then it will display Ready to Start in the cell that the formula is in which would be whatever row is next in line.

 

Your alert can be based on that formula column changing to "Ready to start" and send the notification to whomever is in the contact column for the row.

 

Dave Robinson

Thank you so much, Paul!! I'm implementing this logic in our template files now.

I was so caught up trying to get MATCH(row_id, pred:pred) to work that I didn't even consider working with MATCH(pred, row_id:row_id). Your solution is better than my planned solution, too - your way supports multiple rows with the same predecessor, where my solution wouldn't have done so. (I don't think it's going to work with one row having multiple predecessors, but my solution wouldn't have worked in that case either.)

Frankly, Smartsheets should implement native functionality for working with Predecessors/Successors. There have been some community posts here about how helpful it would be... this is the first programmatic work-around for "next in line" notification that I've seen after some digging. I recommend anyone who comes across this thread submit a Product Enhancement Request: https://app.smartsheet.com/b/form?EQBCT=739aa75f30ca43a8a22eb53e4da7d409

 

THANK YOU AGAIN!

Paul Newcome

Happy to help! 

 

I have yet to find any easy solution for multiple predecessors simply because parsing in SS is such a royal pain. If you can parse them out into their own columns, then you can use an AND function to combine multiple INDEX/MATCH formulas together.

 

As a side note... If you wanted to hide that column, you could change it to a checkbox type column and replace "text" with 1 (notice the lack of quotes around the number - that was intentional).

 

You could then use conditional formatting to highlight the task that is ready to be started and automate an Alert to send based on that checkbox becoming checked to send to the contact in that row. You can combine that with placeholders to make a clean looking email and not include any actual columns in it (gets rid of the ugly grid that usually sends).

Dan Boody

In case anyone sees this and is looking for more on the Row # issue, I thought I'd add this-

Some of the other solutions stop working when a new row is inserted at the top, because any @1 in the formula becomes @2.

Others could definitely have suggested something like this already, but what I started doing is adding the auto-generated Row ID field, which automatically gives a unique number to every row created. Then in the next column, use the formula =Match([Row ID]@row, [Row ID]:[Row ID], 0). That should always give the accurate row #, even if you insert or move rows. Then you can reference that field in other formulas as needed ([Row #]@row).