How to pass Row Number to MATCH function?
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.
- 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!