Vlookup Help

[E3iron]

I'm new to Vlookup's so i need a bit of help, to be honest i'm not sure i'm using the correct function.

I have a spreadsheet(attached, not the full sheet). I have a list of 4 products, then quantities, then this is where i've been trying to use Vlookup. This column will change depending on the previous column.

The problem is as you will see from my sheet i have a range of figures i.e 1 -29,999 =10% , 30,000 -59,999 = 6.5% each with there own %.

So if i have 25,000 in C column D should change to 10%.

Can i use Vlookup for this.

Thanks

[Cheeky Charlie]

yes, reorder your charge bands (lowest first) and delete the hyphen and the second number, so 30,000-59,999 becomes 30000

That should do it.

HTH

[contaminated]

Take a look at attached file

I used this function here

=IF(ISERROR(INDEX($L$32:$L$37,MATCH(TRUE,$C7<=$K$32:$K$37,0))),INDEX($L$32:$L$37,MATCH(MAX($K$32:$K$37),$K$32:$K$37,0)),INDEX($L$32:$L$37,MATCH(TRUE,$C7<=$K$32:$K$37,0)))

Confirmed with control + shift + enter

Maybe it is too long but it's working,,,

[E3iron]

Thanks

This works but would it be possible for someone to explain this to me.

[Cheeky Charlie]

I'm not sure why the altnerative was proposed, but my suggestion is distinctly more understandable, robust and futureproof.

CC

[Palmetto]

Array formulas are best avoided whenever other solutions are available.
Go with the first solution and follow the K.I.S.S. principle.

No slight to Contaminated - good job.
Arrays are just too resource hungry to be used much.

[martindwilson]

i agree but i'd go for
=LOOKUP(C7,{0,1,30000,60000,150000,400000,700000},{0,0.1,0.065,0.04,0.0212,0.0225,0.0185})
but aren't the 2.25 and 2.12 reversed?

[Cheeky Charlie]

That's neat and concise, but a pain in the backside when the charge rates change.

I'd recommend keeping it as it is with a separate rates table and retaining the original Vlookup (I would name the rates table as a named range). It's less pretty but it's easier to look after.

CC