Help with a table/database

[dsalmons]

I have a matrix/table of ID's mileages and term durations any help appreciated.

What I want to do is get a figure based on all 3 parameters i.e.

The vehicle ID (fr_ID) = 40203
The Mileage (fr_mileage) = 30
The term (fr_24) = 24 months

fr_ID	fr_year	fr_month	fr_mileage	fr_6	fr_12	fr_18	fr_24	fr_30	fr_36	fr_42	fr_48	fr_54	fr_60
40203	0	0	10	0	16575	14975	13750	12300	11200	10100	9275	8350	7650
40203	0	0	20	0	15725	14150	12975	11625	10600	9525	8700	7850	7200
40203	0	0	30	0	14850	13375	12225	11025	10100	9025	8175	7375	6750
40203	0	0	40	0	14025	12625	11550	10400	9525	8525	7725	6925	6350
40203	0	0	50	0	13275	11900	10850	9800	9000	8025	7275	6525	5950
40203	0	0	60	0	12600	11275	10250	9250	8475	7575	6875	6150	5600
40203	0	0	70	0	11975	10700	9675	8750	8025	7175	6500	5825	5275
40203	0	0	80	0	11375	10125	9150	8250	7575	6775	6175	5500	4975
40203	0	0	90	0	10800	9600	8650	7800	7150	6400	5825	5200	4700
40203	0	0	100	0	10325	9150	8225	7425	6800	6100	5525	4925	4425
40203	0	0	110	0	9850	8725	7800	7050	6450	5775	5250	4675	4175
40203	0	0	120	0	9400	8300	7425	6700	6125	5500	5000	4425	3950
40203	0	0	130	0	9025	7950	7075	6400	5850	5250	4775	4200	3750
40203	0	0	140	0	8675	7625	6775	6100	5600	5025	4550	4000	3575
40203	0	0	150	0	8325	7300	6450	5825	5350	4775	4350	3825	3400
40203	0	0	160	0	8000	7000	6175	5575	5100	4575	4150	3650	3225
40203	0	0	170	0	7675	6700	5900	5325	4875	4375	3975	3475	3050
40203	0	0	180	0	7375	6425	5625	5075	4650	4175	3800	3300	2900
40203	0	0	190	0	7075	6150	5375	4850	4450	3975	3625	3150	2750
40203	0	0	200	0	6800	5875	5125	4625	4250	3800	3450	3000	2625
40204	0	0	10	0	13950	12600	11800	10675	10000	9325	9075	8300	7875
40204	0	0	20	0	13225	12000	11300	10225	9600	8925	8625	7850	7375
40204	0	0	30	0	12625	11475	10800	9800	9200	8500	8175	7400	6925
40204	0	0	40	0	12000	10900	10300	9300	8725	8075	7750	7000	6525
40204	0	0	50	0	11400	10300	9625	8725	8175	7600	7325	6575	6125
40204	0	0	60	0	10875	9750	9050	8200	7675	7150	6900	6200	5750
40204	0	0	70	0	10400	9250	8500	7675	7175	6700	6475	5800	5375
40204	0	0	80	0	9925	8775	8000	7225	6750	6275	6050	5425	5025
40204	0	0	90	0	9475	8325	7500	6775	6350	5875	5625	5050	4675
40204	0	0	100	0	9100	7925	7100	6400	5975	5500	5250	4700	4375
40204	0	0	110	0	8725	7575	6700	6025	5600	5150	4900	4400	4100
40204	0	0	120	0	8375	7225	6350	5675	5275	4825	4575	4125	3825
40204	0	0	130	0	8050	6875	6000	5375	4975	4550	4300	3850	3575
40204	0	0	140	0	7725	6550	5650	5075	4700	4300	4050	3625	3350
40204	0	0	150	0	7425	6250	5350	4800	4450	4050	3800	3400	3150
40204	0	0	160	0	7125	5975	5050	4525	4200	3800	3575	3200	2950
40204	0	0	170	0	6825	5700	4775	4275	3975	3600	3350	3000	2775
40204	0	0	180	0	6550	5425	4525	4050	3750	3375	3150	2825	2625
40204	0	0	190	0	6300	5175	4275	3825	3550	3200	2975	2650	2450
40204	0	0	200	0	6050	4950	4025	3625	3350	3000	2800	2500	2300
40205	0	0	10	0	14700	13300	12450	11275	10550	9850	9550	8725	8275
40205	0	0	20	0	13950	12675	11925	10800	10150	9425	9100	8250	7775

<colgroup><col width="64" span="14" style="width:48pt"> </colgroup><tbody>
</tbody>

​

 

[Kyle123]

You can either do this with a complex formula, but I'd prefer sticking a helper column on your data that concatenates the fr_ID and fr_Mileage - you can then do a simple index/match or Vlookup.

Here's one formula that will do that you want - this can be much improved upon

=SUM(IF(A2:A43=40203,1,0)*IF(D2:D43=30,1,0)*OFFSET(F2:F43,0,MATCH("fr_24",F1:N1,0)-1))

Needs entering with Ctrl+Shift and Enter