Using a "range" with Vlookup 1

[ReineM]

I need to return a bonus amount based on what a saleperson sold a product for. I can't get it to work with an IF statement...I think there are too many variables. I'm not sure how to make this work with VLOOKUP either because my list is a range:

Sales Bonus
$1.00-5.00 $0
$6.00-15.00 $1
$16.00-25.00 $3

Is there a way to to this with VLookup? Is there a better way?

Thank you!

 

[KenWright]

Do it just the way you are doing it, but only use the upper limits of each your ranges. Make sure you don't set the optional 4th argument in VLOOKUP to FALSE either, it needs to be TRUE or 1 or omitted.

There's an FAQ on how VLOOKUP works that explains the 4th arg bit if you need it.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[ReineM]

Thanks Ken....do I need to use "<=" (less than or equal to) if I am not specifying the range?

 

[KenWright]

Syntax
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Nope, omitting the final argument in VLOOKUP means that If VLOOKUP can't find lookup_value, and range_lookup is TRUE, 1 or omitted, it uses the largest value that is less than or equal to lookup_value.

Would need to adjust your values by a marginal amount though, eg:-

A B
2 0.000 0
3 5.001 1
4 15.001 3
5 25.001 3

Assuming lookup value is in say cell D7 then use

=VLOOKUP(D7,A2:B5,2)

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------