IF and Index-Match for status

I am trying to design a cross-sheet formula that will accomplish the following:

1) Source sheet looks at the target sheet/Project # range and and matches a row based on Project #

2) inside that row, source sheet checks the "Routing Progress" column (I've named this range {Grant Approval-RoutingProgress} )

3a) if "Routing Progress" in target sheet is "yellow," then source sheet displays "yellow"

3b) If "Routing Progress" in target sheet is "green," then source sheet displays "green"

3c) If "Routing Progress" in target sheet doesn't meet either of those criteria, source sheet stops looking at target sheet and defaults to red.

I have a hunch that I need to combine somehow an Index-Match formula and some nested IF statements, but I'm at a loss. I tried the formula below, but it's returning #UNPARSEABLE.

=IF(INDEX(({Grant Approval-RoutingProgress}), MATCH([Project #]@row, {Project # Range}, 0) = “Yellow”), “Yellow”, IF(INDEX(({Grant Approval-RoutingProgress}), MATCH([Project #]@row, {Project # Range}, 0)) = “Green”), “Green”, “Red”)

Help?

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!