Issue displaying certain symbols in formulas

Jeff Chamberlain

I have the following three formulas in the same column of my sheet that I reference in a dashboard:

1. =INDEX(Accomplishments:Accomplishments, MATCH("current", [Report Class]:[Report Class], 0))
2. =INDEX([RYG Overall]:[RYG Overall], MATCH("Current", [Report Class]:[Report Class], 0))
3. =INDEX([Overall Trending]:[Overall Trending], MATCH("Current", [Report Class]:[Report Class], 0))

As you can see, they are essentially the same formula, but reference different field values. The formula looks for "Current" in the report class, and displays the value in the indexed and matching field. These are then referenced in my dashboard table for week over week trending.

The first one returns the text that is in the "accomplishments" column. Works exactly as expected.

The second one returns a symbol, either a red, yellow, or green ball depending on the symbol in that column. Works exactly as expected.

The third formula returns text representing the value of the symbol in the named column. For instance, if the symbol is a horizontal arrow indicating no change, it outputs the text "Sideways", and not the horizontal arrow. I have the column to be formatted as a symbol, yet it can't work. All internet searches, ChatGPT, and Copilot tell me it should, but it is not working.

I created a helper column for just the trend value, but oddly enough, if I move the ball formula into it, it now converts to text. Plus, I really don't want multiple columns if I can avoid it.

Here is a screen capture where I have the formulas in both columns:

Can anyone advise as to how to correct for this, or do I just need two columns, one for status, one for trend?

FYI - I suspect it is because the column either has the ball symbol, or the arrow symbols, but I'm looking to see if there is a work around somehow.

Nico Mahnke

Hello,

your symbol column likely uses a different label set than your source ("Unchanged" instead of "Sideways", etc.). You can try to keep the target icon by wrapping the INDEX/MATCH result in a SWITCH that translates the source text into the label your target symbol set actually expects:

=SWITCH(INDEX([Overall Trending]:[Overall Trending], MATCH("Current", [Report Class]:[Report Class], 0)),
"Up", "Up",
"Down", "Down",
"Sideways", "Unchanged")

Replace "Unchanged" with whatever the third label actually is in your target set's dropdown. This way you don't need two separate columns (status + trend) - one formula column works fine once the labels match exactly.

Kind regards