Spreadsheet: 594514d1539803868-index-and-match-formulas-based-on-two-tables-and-condition-bonus-sales
Sheet: Sheet1

 | Table 1 |  |  |  |  |  |  |  |  | Table 2
 | Profit = 0 to 10% |  |  |  |  |  |  |  |  | Profit >10%
 | Sales Growth %age |  |  |  |  |  |  |  |  | Sales Growth %age
 | -10% to | -5% to | 0% to | 5% to | 10% to | 20% to | 30% + |  |  | -10% to | -5% to | 0% to | 5% to | 10% to | 20% to | 30% +
Sales | -0.1 | -0.05 | 0 | 0.05 | 0.1 | 0.2 | 0.3 |  | Sales | -0.1 | -0.05 | 0 | 0.05 | 0.1 | 0.2 | 0.3 | Sales
500000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |  | 500000 | 0 | 0 | 0 | 0 | 0 | 0 | 0
1000000 | 0.08 | 0.12 | 0.16 | 0.18 | 0.2 | 0.22 | 0.24 |  | 1000000 | 0.08 | 0.12 | 0.17 | 0.19 | 0.21 | 0.23 | 0.25 | 500,000 to 1,000,000
1500000 |  |  |  |  |  |  |  |  | 1500000 |  |  |  |  |  |  |  | 1,000,001 to 1,500,000
2000000 |  |  |  |  |  |  |  |  | 2000000 |  |  |  |  |  |  |  | 1,500,001 to 2,000,000
2500000 |  |  |  |  |  |  |  |  | 2500000 |  |  |  |  |  |  |  | 2,000,001 to 2,500,000
3000000 |  |  |  |  |  |  |  |  | 3000000 |  |  |  |  |  |  |  | 2,500,001 to 3,000,000
3500000 |  |  |  |  |  |  |  |  | 3500000 |  |  |  |  |  |  |  | 3,000,001 to 3,500,000
4000000 |  |  |  |  |  |  |  |  | 4000000 |  |  |  |  |  |  |  | 3,500,001 to 4,000,000
4500000 |  |  |  |  |  |  |  |  | 4500000 |  |  |  |  |  |  |  | 4,000,001 to 4,500,000
5000000 |  |  |  |  |  |  |  |  | 5000000 |  |  |  |  |  |  |  | 4,500,001 to 5,000,000
99999999 |  |  |  |  |  |  |  |  | 99999999 |  |  |  |  |  |  |  | 5,000,001 and more
Data | Input | Input | Input | Input | Input | [NAME] | [NAME] | [NAME] | From Tables above | [NAME]
Client # | Start Date | Sales | Cash-3 years if Sales is >500k & Growth >-10% | Sales Growth | Profit/(Loss) | Calculation method | Average Profit | Profit % | Bonus Factor only if Proift %>0% | Bonus |  |  |  |  | G formula |  | J
19 | 1995-03-01 00:00:00 | 735995 | 2492068 | -0.06 | 554819 |  |  |  |  |  | Table 2 | Factor Cell K6 |  |  | 3 |  | 0.08
228 | 1979-01-01 00:00:00 | 655543 | 2712219 | 0.04 | 604393 |  |  |  |  |  | Table 2 | Factor [NAME] |  |  | 3 |  | 0.17
279 | 1981-05-01 00:00:00 | 571666 | 1766640 | -0.05 | 256491 |  |  |  |  |  | Table 2 | Factor [NAME] |  |  | 3 |  | 0.12
423 | 1984-10-01 00:00:00 | 2808091 | 8301842 | 0.09 | 4516364 |  |  |  |  |  | Table 2 | Factor Cell N10 |  |  | 3 |  | 0.28
430 | 1984-12-01 00:00:00 | 1096229 | 2921043 | 0.02 | 1276917 |  |  |  |  |  | Table 2 | Factor Cell N10 |  |  | 3 |  | 0.24
737 | 1994-07-15 00:00:00 | 1171496 | 3855705 | -0.06 | -1086840 |  |  |  |  |  | Loss |  |  |  | 3 |  | 0
819 | 1997-07-01 00:00:00 | 401461 | 0 | 0.09 | 0 |  | 0 | 0 |  |  | Sales less than 500,000 |  |  |  | 0 |  | 0
847 | 1997-01-01 00:00:00 | 606567 | 0 | -0.13 | 0 |  | 0 | 0 |  |  | Growth < -10% |  |  |  | 0 |  | 0
1823 | 2017-04-04 00:00:00 | 994035.56 | 2382991 | 0.43 | 209583 |  |  |  |  |  | Table 1 | Factor [NAME] |  |  | 2 |  | 0.24
1855 | 2018-04-16 00:00:00 | 667315 | 878456 | 0 | 237756 |  |  |  |  |  | First year - Table 2 Factor Cell M6 |  |  |  | 1 |  | 0.17
650 | 2002-09-16 00:00:00 | 3895654 | 4635911 | 0.1 | 1500156 |  |  |  |  |  | Table 2 | Factor [NAME] |  |  | 3 |  | 0.32
Calculation Method - 3 | Start Date should be within 3 years i.e 2016 and prior |  |  |  |  |  |  |  | Bonus Factor =
 | Minimum Sales should be 500,000 |  |  |  |  |  |  |  | Profit% should be >0
 | sales Growth should not be less than  -10% |  |  |  |  |  |  |  | If profit % is < 10% Look into Table 1
 |  |  |  |  |  |  |  |  | Find Sales Growth Column
Calculation Method - 2 | Start Date should be within 2 years i.e 2017 and prior |  |  |  |  |  |  |  | Find Sales volumn Row
 | Minimum Sales should be 500,000 |  |  |  |  |  |  |  | Pick the factor
 | sales Growth should not be less than  -10%
 |  |  |  |  |  |  |  |  | Bonus Factor =
Calculation Method - 1 | Start Date should be greater than 2 years i.e 2016 and prior |  |  |  |  |  |  |  | Profit% should be >0
 | Minimum Sales should be 500,000 |  |  |  |  |  |  |  | If profit % is > 10% Look into Table 2
 | sales Growth should not be less than  -10% |  |  |  | ??? |  |  |  | Find Sales Growth Column
 |  |  |  |  |  |  |  |  | Find Sales volumn Row
If no conditions met, zero bonus |  |  |  |  |  |  |  |  | Pick the factor