MATCH / INDEX to return a value

Craig Ostrander

Apologies what may be a basic question. I just finished training and tried to figure this out from the Formula Handbook, but struggling with the learning curve and understanding the equations.

From my snapshot above, I'm trying to search the "Milestone Status" Column for a specific value and return the "Task" name. In the snap shot above, the result should be "Discovery".

It looks like I need a combination of INDEX and MATCH, but keep getting an UNPARESABLE error.

Paul Newcome

Try this:

=INDEX(Tasks:Tasks, MATCH("In Progress", [Milestone Status]:[Milestone Status], 0))

SteyJ

I think you'll want to use join/collect here.

=JOIN(COLLECT({Task}, {Milestone Status}, "Specific Value Here"), CHAR(10))

You will want to put this in a separate sheet in it's own cell (not column formula unless you are using @row for the "Specific Value Here" and update the references. Then, change the cell to wrap text and it will populate all tasks which meet the criteria.

Craig Ostrander

SteyJ

Thanks; if nothing else, learned about the COLLECT function. I'm not wanting to join diff columns, tho.

I'm wanting to search the "Milestone Status" column for the row that has the value "In Progress" and return the value of what the "Task" column value is. The "In Process" will be assigned at the PARENT level. We're designing our process so we'll only have one PARENT row labeled "In Process".

Using Excel terminology, I want to do a VLOOKUP on the "Milestone Status" column, searching for the value "In Progress", and return the value found under the "Task" column for that row.

Finally, I want the formula to be in my Sheet Summary so that I can report on that value.

Paul Newcome

Try this:

=INDEX(Tasks:Tasks, MATCH("In Progress", [Milestone Status]:[Milestone Status], 0))

Craig Ostrander

BINGO!!! Thanks Paul!

Paul Newcome

Happy to help. 👍️