Using VLookUp

[Guest]

Hi

I have a table that looks like this

Units Discoun
10 1
20 2
30 3

I would like to use =vlookup(C1,table,2,TRUE). If I have a unit of 18 in C1, the formula will show 1% instead of 2%. I'd like it to show 2% because for anything above 10, it shld show 2% and for anything above 20, it shld show 3%. May I know which is the best way to do this

Thanks

 

[Arvi Laanemets]

Hi

=VLOOKUP(9+C1,table,2)

But when the pattern I can follow in your lookup table is consistent, then
you can do without lookup table at all
=CEILING(C1/1000,0.01)
will do


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets

Hi,

I have a table that looks like this:

Units Discount
10 1%
20 2%
30 3%

I would like to use =vlookup(C1,table,2,TRUE). If I have a unit of 18 in

C1, the formula will show 1% instead of 2%. I'd like it to show 2% because
for anything above 10, it shld show 2% and for anything above 20, it shld
show 3%. May I know which is the best way to do this?

 

[Bob Phillips]

Re-structure the lookup table as

0 1%
10 2%
20 3%

make sure you have a catch-all top value.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi,

I have a table that looks like this:

Units Discount
10 1%
20 2%
30 3%

I would like to use =vlookup(C1,table,2,TRUE). If I have a unit of 18 in

C1, the formula will show 1% instead of 2%. I'd like it to show 2% because
for anything above 10, it shld show 2% and for anything above 20, it shld
show 3%. May I know which is the best way to do this?

 

[Frank Kabel]

Hi
one way:
1. sort your lookup range in descending order: so put 30 as first row
and 10 as last row
2. Use the following formula
=INDEX(B1:B3,MATCH(C1,A1:A3,-1))


--
Regards
Frank Kabel
Frankfurt, Germany

Hi,

I have a table that looks like this:

Units Discount
10 1%
20 2%
30 3%

I would like to use =vlookup(C1,table,2,TRUE). If I have a unit of 18

in C1, the formula will show 1% instead of 2%. I'd like it to show 2%
because for anything above 10, it shld show 2% and for anything above
20, it shld show 3%. May I know which is the best way to do this?

 

[JE McGimpsey]

Change your table to

Units Discount
1 1%
11 2%
21 3%

 

[JE McGimpsey]

Note that this will give you a 2% discount for 10-19, inclusive, rather
than strictly "above 10" - which may be what the OP intended.

 

[JE McGimpsey]

in that case, I'll take 100,000 units, please...

 

[Arvi Laanemets]

Hi

in that case, I'll take 100,000 units, please...

*But when the pattern is consistent*
)