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 copypasted "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 "TESTIFZERO".
=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; "TESTIFZERO")))); 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?
Best Answer

Try looking for an exact match in the VLOOKUP that pulls the bonus number.
Answers

Hi @Rytis Karalius ,
In reviewing your formula, I suspect that the issue may have to do with not having a match_type included with any of the VLOOKUP statements. Even though it's listed as an optional argument, it's possible that adding in the “false” match_type will help to ensure they’re looking for exact matches.
More information on working with the VLOOKUP function can be found in our Help article here. Do let us know if adding in the false match_type doesn't resolve the issue and we'll be happy to look into this further. If the issue persists, I also recommend reaching out to our Support Team via https://help.smartsheet.com/contact/smartsheetapp as they will be the best resource for troubleshooting.
Thanks,
Ben

I agree that one of the first steps would be to enter a Match Type in your VLOOKUP functions since it is defaulted to an approximate match when it is left out.

Well, i thought the match type should be left default as "true", because what we are searching for  is the approximate values of "Bonus", then adding bonus as a fixed number or as a multiplier based on bonus size, i cannot change it to "False", because there are no exact matches.

Are you able to show a screenshot of the source data for the VLOOKUP?

Sure thing, @Paul Newcome, thank you for helping me with this.
I did post it in the first post, but probably did not make it clear.
Sums and Bonuses are calculated in this table:
VLOOKUP takes Sum from first table and is searching for approximate values in second table (this one)
, based on range, that is determined by "Bonus level". It first checks wether bonus level is "1" in another sheet, then I.E. If SUM is between 0  200 it gives "0" as a bonus. If sum is 200  700 it gives 45 as a bonus and so on. If sum is 2900  4000  it multiplies 0.03 * Sum from first table.
If the Bonus is level 2  formula does the same with Bonus Level 2 range values.

Try looking for an exact match in the VLOOKUP that pulls the bonus number.

Holy Cows! This actually worked! Thank you very much.
All bonuses are calculated perfectly now!

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!