Spreadsheet: 594503d1539799272-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
Sales | -10% to | -5% to | 0% to | 5% to | 10% to | 20% to | 30% + |  | Sales | -10% to | -5% to | 0% to | 5% to | 10% to | 20% to | 30% +
500,000 to 1,000,000 | 0.08 | 0.12 | 0.16 | 0.18 | 0.2 | 0.22 | 0.24 |  | 500,000 to 1,000,000 | 0.08 | 0.12 | 0.17 | 0.19 | 0.21 | 0.23 | 0.25
1,000,001 to 1,500,000 |  |  |  |  |  |  |  |  | 1,000,001 to 1,500,000
1,500,001 to 2,000,000 |  |  |  |  |  |  |  |  | 1,500,001 to 2,000,000
2,000,001 to 2,500,000 |  |  |  |  |  |  |  |  | 2,000,001 to 2,500,000
2,500,001 to 3,000,000 |  |  |  |  |  |  |  |  | 2,500,001 to 3,000,000
3,000,001 to 3,500,000 |  |  |  |  |  |  |  |  | 3,000,001 to 3,500,000
3,500,001 to 4,000,000 |  |  |  |  |  |  |  |  | 3,500,001 to 4,000,000
4,000,001 to 4,500,000 |  |  |  |  |  |  |  |  | 4,000,001 to 4,500,000
4,500,001 to 5,000,000 |  |  |  |  |  |  |  |  | 4,500,001 to 5,000,000
5,000,001 and more |  |  |  |  |  |  |  |  | 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
19 | 1995-03-01 00:00:00 | 735995 | 2492068 | -0.06 | 554819 | 3 |  |  | 0.08 |  | Table 2 | Factor Cell K6
228 | 1979-01-01 00:00:00 | 655543 | 2712219 | 0.04 | 604393 | 3 |  |  | 0.17 |  | Table 2 | Factor [NAME]
279 | 1981-05-01 00:00:00 | 571666 | 1766640 | -0.05 | 256491 | 3 |  |  | 0.12 |  | Table 2 | Factor [NAME]
423 | 1984-10-01 00:00:00 | 2808091 | 8301842 | 0.09 | 4516364 | 3 |  |  | 0.28 |  | Table 2 | Factor Cell N10
430 | 1984-12-01 00:00:00 | 1096229 | 2921043 | 0.02 | 1276917 | 3 |  |  | 0.24 |  | Table 2 | Factor Cell N10
737 | 1994-07-15 00:00:00 | 1171496 | 3855705 | -0.06 | -1086840 | 3 |  |  | 0 |  | Loss
819 | 1997-07-01 00:00:00 | 401461 | 0 | 0.09 | 0 | 0 | 0 | 0 | 0 |  | Sales less than 500,000
847 | 1997-01-01 00:00:00 | 606567 | 0 | -0.13 | 0 | 0 | 0 | 0 | 0 |  | Growth < -10%
1823 | 2017-04-04 00:00:00 | 994035.56 | 2382991 | 0.43 | 209583 | 2 |  |  | 0.24 |  | Table 1 | Factor [NAME]
1855 | 2018-04-16 00:00:00 | 667315 | 878456 | 0 | 237756 | 1 |  |  | 0.17 |  | First year - Table 2 Factor Cell M6
650 | 2002-09-16 00:00:00 | 3895654 | 4635911 | 0.1 | 1500156 | 3 |  |  | 0.32 |  | Table 2 | Factor [NAME]
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