LookUp Function

[Chrisj20]

Trying to use the above to insert an amount which will depend on the value in
another cell.
i.e. in A1 is the price and in A2 I need the figure which is dependant on
the amount in A1. If A1 is less than 125000 A2 will be 0; between 125001 and
250000 the figure in A2 will be 1% of A1; between 250001 and 500000 the
figure in A2 will be 3% of A1 etc.
Similar problem with another cell where I need to lookup an amount from a
table i.e. if A1 is between 2 amounts the figure in A3 will be a sum which is
set out in a table.
Any help would be much appreciated.

 

[Pete_UK]

Set up a small table like this somewhere (I put it in M1:N4):

0 0%
125001 1%
250001 3%
500001 5%

Then, with the amount in A1, you can use this in A2 (B1 might be a
better location):

=VLOOKUP(A1,M$1:N$4,2)*A1

Sometimes these percentage splits are on a sliding scale, but this
formula does not model that - not clear from your description.

Hope this helps.

Pete

 

[Roger Govier]

Hi Chris

You need to set up a table with your ranges and the values associated with
them.
For example, on sheet 2 in columns A and B set up
A B
0 0
12501 1%
25001 2%
50001 3%
..
etc.

On sheet1 in cell A2
=A1*VLOOKUP(A1,Sheet2!A:B,2,1)

The final parameter of Vlookup, 1 or True, means that it will return the
value from column B from the highest value of column A that does not exceed
the lookup value.