# Formula for Comparisons and Subtraction

#### Glasgowsmile

##### Active Member
Good Morning,

I've posted a photo so you can see what I'm doing but basically...

I want to compare two columns and then subtract the numbers next to them if they match.

I want to compare column K and column N, find the matches and then subtract column L from column O to paste into column Q.

https://imgur.com/kwG55fr

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### AlanY

##### Well-known Member
may be

Book1
KLMNOPQ
1Comparison
2A12B610
3B10D44
4C5C8-3
5D9A25
6E8E53
7F7F16
8G3H10
9H6I75
10I5G3-2
Sheet2
Cell Formulas
RangeFormula
Q2=L2-INDEX(\$O\$2:\$O\$10,MATCH(K2,\$N\$2:\$N\$10,0))

#### Glasgowsmile

##### Active Member
This is great, how could I expand this if I increase the data set?

For example, let's say K:L are Week 1, N:O are Week 2. What if I wanted to add a Week 3 / 4 and it compares across all 4 weeks what the changes are?

#### AlanY

##### Well-known Member
it's basically just copy the formula across

Book1
JKLMNOPQRSTUVWX
1Comparison
2Wk1Wk2Wk3Wk4Wk1 > Wk2Wk2 > Wk3Wk3 > Wk4
3A12B6A6B1110-45
4B10D4B1D1145-10
5C5C8C2C5-36-3
6D9A2D9A15-5-2
7E8E5E6E13-15
8F7F1F9F116-8-2
9G3H1G10H10-77
10H6I7H12I105-1111
11I5G3I4G3-23-6
Sheet1
Cell Formulas
RangeFormula
V3=INDEX(\$K\$3:\$K\$11,MATCH(\$J3,\$J\$3:\$J\$11,0))-INDEX(\$N\$3:\$N\$11,MATCH(\$J3,\$M\$3:\$M\$11,0))
W3=INDEX(\$N\$3:\$N\$11,MATCH(\$J3,\$M\$3:\$M\$11,0))-INDEX(\$Q\$3:\$Q\$11,MATCH(\$J3,\$P\$3:\$P\$11,0))
X3=INDEX(\$Q\$3:\$Q\$11,MATCH(\$J3,\$P\$3:\$P\$11,0))-INDEX(\$T\$3:\$T\$11,MATCH(\$J3,\$S\$3:\$S\$11,0))

#### Glasgowsmile

##### Active Member
Not quite what I had in mind, what if I wanted a column that was Week 1 vs Week 2 Comparison and then another Column that compared Week 1 to Week 2 & 3, and then a 3rd that compared Week 1 to 2, 3, & 4?

#### AlanY

##### Well-known Member
Not quite what I had in mind, what if I wanted a column that was Week 1 vs Week 2 Comparison and then another Column that compared Week 1 to Week 2 & 3, and then a 3rd that compared Week 1 to 2, 3, & 4?

say for A

Wk1 10
Wk2 9
Wk3 8
Wk4 7

what results are you expected for compared Week 1 to Week 2 & 3, and
Week 1 to 2, 3, & 4 ?

#### Glasgowsmile

##### Active Member
For example for A, I'm trying to see how A has grown or declined over the last 3 weeks compared to week 1 - so I want to compared week 1 against all other weeks to pull A-G, in this example, so I can see how it stacks up over time.

#### AlanY

##### Well-known Member
ok, have a look of this

Book1
JKLMNOPQRSTUVWX
1Comparison
2Wk1Wk2Wk3Wk4Wk2Wk3Wk4
3A12B6A6B1110611
4B10D4B1D1149-1
5C5C8C2C5-330
6D9A2D9A150-2
7E8E5E6E1327
8F7F1F9F116-2-4
9G3H1G10H10-70
10H6I7H12I105-65
11I5G3I4G3-21-5
Sheet1
Cell Formulas
RangeFormula
V3=INDEX(\$K\$3:\$K\$11,MATCH(\$J3,\$J\$3:\$J\$11,0))-INDEX(\$N\$3:\$N\$11,MATCH(\$J3,\$M\$3:\$M\$11,0))
W3=INDEX(\$K\$3:\$K\$11,MATCH(\$J3,\$J\$3:\$J\$11,0))-INDEX(\$Q\$3:\$Q\$11,MATCH(\$J3,\$P\$3:\$P\$11,0))
X3=INDEX(\$K\$3:\$K\$11,MATCH(\$J3,\$J\$3:\$J\$11,0))-INDEX(\$T\$3:\$T\$11,MATCH(\$J3,\$S\$3:\$S\$11,0))

Replies
1
Views
112
Replies
3
Views
119
Replies
1
Views
114
Replies
10
Views
147
Replies
2
Views
150

1,195,598
Messages
6,010,646
Members
441,558
Latest member
lambierules

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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