IF and Index-Match for status

Amanda Paveglio

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?

Paul Newcome

You have some misplaced parenthesis plus you have some "smart quotes". See the quotes that are slanted to show open quote vs closed quote? Those come from programs such as Word, and Smartsheet can't read them. You will need to replace them either by typing directly into SS or a different program such as Notepad (or here in the Community).

Try this one...

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

Amanda Paveglio

Hi Paul, thanks for the quick response and the tip about "smart quotes" vs. plain quotes. I'll keep that in mind.

Your suggested formula now yields an "#INVALID REF" error, which tells me that Smartsheet can now USE (parse) the formula, it's just looking at the wrong place now. (?) So that's progress. I'll keep poking at it.

(UPDATE A FEW MINS LATER: I fixed it. The {Project # Range] was pointing at a range in the source sheet instead of the target sheet. Ta-dah! Thanks for your help!)

Paul Newcome

Happy to help. 👍️