Complex formula is calculating random cells and skipping others.

Hi everybody,

So this will be a long read, but i hope somebody will help me solve this craziness, that already ate 2 days of my life.

So i have this formula:

=IFERROR(IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 1; IF(VLOOKUP([Sum]1; {Bonusai Range 1}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 1}; 2); VLOOKUP([Suma (be PVM)]1; {Bonusai Range 1}; 2)); IF(VLOOKUP([Seller]1; {Techninis Range Partneris | Bonusas}; 2) = 2; IF(VLOOKUP([Sum]1; {Bonusai Range 2}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 2}; 2); VLOOKUP([Suma (be PVM)]1; {Bonusai Range 2}; 2)); IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 3; IF(VLOOKUP([Sum]1; {Bonusai Range 3}; 2) < 1; [Sum]1 * VLOOKUP([Suma (be PVM)]1; {Bonusai Range 3}; 2); VLOOKUP([Sum]1; {Bonusai Range 3}; 2)); IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 0; 0)))); 0)

Which does the following -

VLOOKUP "Seller" cell and check what Bonus level it has on another sheet ({Range Seller | Bonus}).

Then calculate the bonus based on the level . If level 1... 2... 3... Calculate according to another sheet.

Calculation is pretty simple - IF value / multiplier ">1" then give the "value/multiplier" as a result, if "value/multiplier" is "<1" - then multiply sum * value/multiplier.

This process is repeated 3 times for each level (1, 2, 3).

If the level is set to "0" - Print "0" as a result. All formula is covered with "IFERROR" - print "0".

PROBLEM:

Some cells get calculated, while others - do not.

There are no spelling/grammar/mistype problems because for testing purposes i just copy-pasted "Sellers".

There are no relations with SUM / Name used / Bonus level size or what ever to cause this error.

Seems like an absolute randomness - Cells are getting calculated or no when the first time i save the sheet. After that - only the same cell is calculated. It won't be calculated even if i switch rows.

After some testing i found out, that ALL cells, which are not calculated correctly are randomly treated as having Bonus Level "0" (even though it's 1, 2, or 3), i found this because i've set last condition "If Bonus level is 0, print "TEST-IF-ZERO".

=IFERROR(IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 1; IF(VLOOKUP([Sum]1; {Bonusai Range 1}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 1}; 2); VLOOKUP([Suma (be PVM)]1; {Bonusai Range 1}; 2)); IF(VLOOKUP([Seller]1; {Techninis Range Partneris | Bonusas}; 2) = 2; IF(VLOOKUP([Sum]1; {Bonusai Range 2}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 2}; 2); VLOOKUP([Suma (be PVM)]1; {Bonusai Range 2}; 2)); IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 3; IF(VLOOKUP([Sum]1; {Bonusai Range 3}; 2) < 1; [Sum]1 * VLOOKUP([Suma (be PVM)]1; {Bonusai Range 3}; 2); VLOOKUP([Sum]1; {Bonusai Range 3}; 2)); IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 0; "TEST-IF-ZERO")))); 0)

POSSIBLE SOLUTION:

The only thing that comes to my mind - is that formula is too overcrowded with vlookups, and i need to either simplify it, or simplify it :) Any ideas on what's wrong with this mess?