# Using arrows to show data change between two columns

#### Sly1980

##### New Member
Hi there
I would like to use arrows to show the difference between two columns - the columns have "R", "Y", and "G" to indicate red, yellow, and green in terms of status. The two columns would have status without actions and status with actions - I would like to visually show whether there has been a change as a result of the actions. For example, if the status without actions is "R" and the status with actions become "G", then the arrow should show an upwards pointing green arrow. I know this is easily done with numbers but I don't know whether this can be done with letters.

#### Sly1980

##### New Member
Yes, the ROWS function is generating a number greater than the columns in the INDEX range.
In Sly1980 's example Index formula there were 63 columns B:BL
@Sly1980 you do not need to change the G,Y,R generating index part of the formula when you include in the new Lookup.

I am still not sure what you want. Do you want the Arrow in the Action column to indicate that progress compared to the No Action status, is positive. negative or same?

Does this approach help......
Columns H & I are just for illustration of what the G,Y,R values are.
Column K is colour coded Action Status
Column L is Coloured arrows indicating progress.

Book1
HIJKL
2No ActionAction
3GG10
4GY1-1
5GR1-2
6YY20
7YG21
8YR2-1
9RR30
10RG32
11RY31
12GR1-2
13YY20
14RG32
15RG32
16YY20
17GR1-2
18YY20
19RR30
20YG21
21YR2-1
22
Sheet5
Cell Formulas
RangeFormula
K3:K21K3=IFERROR(LOOKUP(INDEX(Sheet1!B\$141:BL\$141,ROWS(F\$3:F3)),{"G","R","Y"},{1,3,2}),"")
L3:L21L3=IFERROR(K3-(LOOKUP(INDEX(Sheet1!B\$142:BL\$142,ROWS(F\$3:F3)),{"G","R","Y"},{1,3,2})),"")
K22K22=IFERROR(LOOKUP(INDEX(Sheet1!B\$1:BK\$1,ROWS(F\$3:F22)),{"G","R","Y"},{1,3,2}),"")
L22L22=IFERROR(K22-(LOOKUP(INDEX(Sheet1!B\$142:BK\$142,ROWS(F\$3:F22)),{"G","R","Y"},{1,3,2})),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K3:K25Other TypeIcon setNO
L3:L25Other TypeIcon setNO
K3:K25Other TypeIcon setNO

Edit: Ignore the formulas shown for K22 L22 , they are irrelevant.

View attachment 11972

The values can be hidden by narrowing the columns or setting the cell text to white.
Hi Snakehips
Thank you for the response. To avoid confusion, I will outline what I need (we can forget the index formula if it would be easier). I need the data that is entered in the input sheet to show in a summary sheet as traffic lights. Example of the input sheet below. So, only data from the rows with "R", "Y", "G" are pulled through for traffic light conversion. For the names, company, etc, I can still use index as this data is represented as is. The original data needs to additionally, be transposed on the summary sheet. Another complication is that in cases where no "R", "Y", or "G" is entered, I want a traffic light to show this - I have been using a dark grey to denote this.

Adding onto this, my original question in this thread - would it be possible to add a column next to the two columns (status without actions & status with actions) that shows whether there is a difference between the two "action" columns, i.e., if it is two yellows, or two reds, or two greens, it would show a line or dash; if red to yellow, red to green, or yellow to green, it would show a green up arrow; if yellow to red, or green to red, it would show a down arrow. If blank, it would show nothing. I hope that this is possible, if not, hopefully the traffic lights can at least work.

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

#### Snakehips

##### Well-known Member
G,Y,R values in H,I M,N etc as you are doing now.
Formulas as below in F, G, J, K, L, O

Apply Icon Set Conditional Formatting to those columns typically as below.

Book1
FGHIJKLMNO
2No ActionActionNo ActionActionEffectNo ActionActionNo ActionActionEffect
3-10GY10-1YG-1
401YR1-11GR2
50-1YG-1-10GY1
61-1RG-2-11GR2
7-10GY1-1-1GG0
801YR101YR1
Sheet23
Cell Formulas
RangeFormula
F3:G8, K3:L8F3=LOOKUP(H3,{"G","R","Y"},{-1,1,0})
J3:J8, O3:O8J3=IFERROR((LOOKUP(I3,{"G","R","Y"},{1,3,2}))-(LOOKUP(H3,{"G","R","Y"},{1,3,2})),"")

NOTE: The XL2BB above cannot show the conditional formatting.
The actual sheet is as below.

Hope that helps.

#### Sly1980

##### New Member
G,Y,R values in H,I M,N etc as you are doing now.
Formulas as below in F, G, J, K, L, O

Apply Icon Set Conditional Formatting to those columns typically as below.

Book1
FGHIJKLMNO
2No ActionActionNo ActionActionEffectNo ActionActionNo ActionActionEffect
3-10GY10-1YG-1
401YR1-11GR2
50-1YG-1-10GY1
61-1RG-2-11GR2
7-10GY1-1-1GG0
801YR101YR1
Sheet23
Cell Formulas
RangeFormula
F3:G8, K3:L8F3=LOOKUP(H3,{"G","R","Y"},{-1,1,0})
J3:J8, O3:O8J3=IFERROR((LOOKUP(I3,{"G","R","Y"},{1,3,2}))-(LOOKUP(H3,{"G","R","Y"},{1,3,2})),"")

NOTE: The XL2BB above cannot show the conditional formatting.
The actual sheet is as below.

View attachment 11993
View attachment 11995
View attachment 11996
Hope that helps.
Hi Snakehips
This solution works perfectly on the cells I've tried. Thank you very much! I will now convert the worksheet, if I have any issues, I will add a reply on this. Thank you once again!

#### Snakehips

##### Well-known Member
Great! Glad @GraH and I were able to help.

Replies
16
Views
325
Replies
3
Views
104
Replies
4
Views
137
Replies
11
Views
307
Replies
19
Views
552

1,129,829
Messages
5,638,574
Members
417,037
Latest member
vedan2020

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back