Hi,
I have this problem were I would like a formula to return the correct Approval level (column C) based on the "balance" amount (column A) and the "account risk" (column B)
In the table F2:I13 I have an overview where we can see that Approval Level is based on both Account risk and the amount in the balance. An example: A38 has an aprox value of 108 000 000 and "high" risk (B38). According to the table the correct approval level should then be 3 (F7)
I am assuming that the correct formula that solves this is able to:
1. Match Account Risk (B) with Account Risk (G)
2. Based on the account risk, use the balance in column A, matched up agains the interval in column H and then return the appropriate approval level found in column F.
Is there smart way to achieve this? My original sheet has several thousands rows.
I have this problem were I would like a formula to return the correct Approval level (column C) based on the "balance" amount (column A) and the "account risk" (column B)
In the table F2:I13 I have an overview where we can see that Approval Level is based on both Account risk and the amount in the balance. An example: A38 has an aprox value of 108 000 000 and "high" risk (B38). According to the table the correct approval level should then be 3 (F7)
I am assuming that the correct formula that solves this is able to:
1. Match Account Risk (B) with Account Risk (G)
2. Based on the account risk, use the balance in column A, matched up agains the interval in column H and then return the appropriate approval level found in column F.
Is there smart way to achieve this? My original sheet has several thousands rows.
| Problemsheet.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Balance | Account risk | Aproval level ? | Approval level | Account Risk | Balance Interval from | Balance Interval to | ||||
| 2 | 60 000 000 | Medium | 2 | Low | 2 000 000 000 | 500 000 000 | |||||
| 3 | 100 717 150 | Medium | 2 | Medium | 1 000 000 000 | 400 000 000 | |||||
| 4 | 291 801 382 | Medium | 2 | High | 500 000 000 | 200 000 000 | |||||
| 5 | 76 597 697 | Low | 3 | Low | 500 000 000 | 160 000 000 | |||||
| 6 | 72 889 993 | Low | 3 | Medium | 400 000 000 | 80 000 000 | |||||
| 7 | 338 621 207 | Low | 3 | High | 200 000 000 | 40 000 000 | |||||
| 8 | 56 570 126 | Medium | 4 | Low | 160 000 000 | 80 000 000 | |||||
| 9 | 53 719 560 | Low | 4 | Medium | 80 000 000 | 50 000 000 | |||||
| 10 | 60 109 142 | Low | 4 | High | 40 000 000 | 30 000 000 | |||||
| 11 | 71 907 272 | Medium | 5 | Low | 60 000 000 | 0 | |||||
| 12 | 73 540 327 | Low | 5 | Medium | 50 000 000 | 0 | |||||
| 13 | 50 152 000 | Medium | 5 | High | 30 000 000 | 0 | |||||
| 14 | 133 394 082 | Medium | |||||||||
| 15 | 120 912 120 | Medium | |||||||||
| 16 | 71 192 542 | Low | |||||||||
| 17 | 67 208 449 | Low | |||||||||
| 18 | 153 204 148 | Low | |||||||||
| 19 | 127 731 123 | Low | |||||||||
| 20 | 122 341 007 | Low | |||||||||
| 21 | 66 739 017 | Medium | |||||||||
| 22 | 244 694 674 | Medium | |||||||||
| 23 | 55 150 944 | Low | |||||||||
| 24 | 70 997 260 | Low | |||||||||
| 25 | 171 335 580 | Low | |||||||||
| 26 | 94 887 353 | Medium | |||||||||
| 27 | 80 941 399 | Medium | |||||||||
| 28 | 56 940 213 | Medium | |||||||||
| 29 | 57 984 639 | Low | |||||||||
| 30 | 63 337 692 | Low | |||||||||
| 31 | 64 633 865 | Low | |||||||||
| 32 | 289 969 685 | Low | |||||||||
| 33 | 67 656 552 | Low | |||||||||
| 34 | 174 817 557 | Low | |||||||||
| 35 | 50 070 706 | Medium | |||||||||
| 36 | 51 186 055 | Low | |||||||||
| 37 | 73 146 922 | Low | |||||||||
| 38 | 108 070 743 | high | Should return the value 3 | ||||||||
| 39 | 170 799 567 | Low | |||||||||
Sheet1 | |||||||||||
