Difficult excel formular with many factors

New Contributor

Hi all,

it's the first time here for getting your support and really hope to get an answer which brings me forward.

The task is to get a result which is influenced of following data:

A sales manager (SM) gets a bonus based on a sales contract value as long as this value match the standard price list. If the value is higher than the basic value than it rates with a factor.


a) Contract Value = 10.000 (A1) and standard price list = 10.000 (A2) then factor is 1,0 and result need to be shown in (A3) 

b) Contract Value (A1) is >1,02 and <1,04 of standard price list (A2) then factor is 1,2; or >1,04 and <1,05 the factor is 1,5; or >1,06  and <1,08 the factor is 1,8; or =/>1,08 the factor is 2,0 and result need to be also shown in (A3)


Anybody with an idea? Thanks for thinking about and comments.




4 Replies


I'd use VLOOKUP (or XLOOKUP) in combination with a lookup table.

See the attached demo.

@Hans Vogelaar 


Hi Hans,


thanks for your comment and proposal. The numbers 1,03 / 1,05 ... I can add in between to get the result which is fine.

Do you know how I can solve the problem for the last bonus step as here I have to count 1,08 and any higher value, means = and > of 1,08?




The formula should already handle that. Have you tried it?

I have adjusted it to be on the save side with another line and >1,08 and yes everything works perfectly. Thank you so much for your support. have a nice evening. Best Ralf