[h=2]Table, sumif for index(match())[/h]
I would attach the excel file, but the forum does not allow?
Gentleman, I've tried other topics and matrix lookup with sumif but didnt find a solution for my case, some one have a glue on how to do that?
Table 1
Product State Condition Discount Price 1 Price 2 Price 3
Car 1 TX Used 1% 4362 5962 4737
Car 1 TX Used 2% 4123 5961 9397
Car 2 TX Used 3% 4660 3745 7282
Car 2 TX Used 4% 1063 5305 6737
Car 1 TX New 1% 3528 6394 9192
Car 1 TX New 2% 3404 3503 9795
Car 2 TX New 3% 2463 3726 2026
Car 2 TX New 4% 6218 3784 9580
Car 1 NY Used 1% 2328 5156 2075
Car 1 NY Used 2% 2511 8106 7857
Car 2 NY Used 3% 4344 8190 7571
Car 2 NY Used 4% 6885 1667 4039
Car 1 NY New 1% 1233 2465 7968
Car 1 NY New 2% 6952 7070 3265
Car 2 NY New 3% 1141 3097 4122
Car 2 NY New 4% 9703 3208 7431
Table 2
Product State Condition Discount Column2 Column1 Price 1 Price 2 Price 3
Car 1 TX Used 8485
Car 2 TX New 6932
formula in Price 1 column Car 1 row
sum if [table 2],[Product]= [table 1],[Product] and [table 2],[State] = [table 1],[State] and [table 2],[Condition] =[table 1],[Condition] and [table 2],[Headers],[Price 1]=match([Table 1][All][Headers] in this case the formula would be in the Price 1 column and Car 1 row
Table 1
Product State Condition Discount Price 1 Price 2 Price 3
Car 1 TX Used 1% 4362 5962 4737
Car 1 TX Used 2% 4123 5961 9397
Car 2 TX Used 3% 4660 3745 7282
Car 2 TX Used 4% 1063 5305 6737
Car 1 TX New 1% 3528 6394 9192
Car 1 TX New 2% 3404 3503 9795
Car 2 TX New 3% 2463 3726 2026
Car 2 TX New 4% 6218 3784 9580
Car 1 NY Used 1% 2328 5156 2075
Car 1 NY Used 2% 2511 8106 7857
Car 2 NY Used 3% 4344 8190 7571
Car 2 NY Used 4% 6885 1667 4039
Car 1 NY New 1% 1233 2465 7968
Car 1 NY New 2% 6952 7070 3265
Car 2 NY New 3% 1141 3097 4122
Car 2 NY New 4% 9703 3208 7431
Table 2
Product State Condition Discount Column2 Column1 Price 1 Price 2 Price 3
Car 1 TX Used 8485
Car 2 TX New 6932
formula in Price 1 column Car 1 row
sum if [table 2],[Product]= [table 1],[Product] and [table 2],[State] = [table 1],[State] and [table 2],[Condition] =[table 1],[Condition] and [table 2],[Headers],[Price 1]=match([Table 1][All][Headers] in this case the formula would be in the Price 1 column and Car 1 row
I would attach the excel file, but the forum does not allow?