Lookups and ifs!

[Annie]

Hi I have the following workbook:

Sheet 1 contains Tax Rates

A1 B1 C1
20% 40% 60%

Sheet 1 further down the sheet contains the following ranges:

A10 B10 C10 D10
<10000 10000 - 20000 20001 - 30000 30001 - 40000

In another sheet I have a salary cell which I type a figure into. What I
want to do is have Excel look at this value and then whichever range in row
10 it appears, Excel can then determine which tax rate to mulitply it by in
row 1>
Hope that is clear.....
Many thanks

 

[Eduardo]

Hi,
I assume you enter the amount in cell A1
not sure about the ranges if <10000 is 20% then up to 20000 40% and more
than 30000 60% if yes use

=+A1*SUMPRODUCT(--(A1>{0;10000;20000;30000}),{0.2;0.2;0.2;0})

 

[Roger Govier]

Hi Annie

You need to change your layout, so the tax rates belong to categories
Enter in A1 0, A2 10001, A3 20001, A4 30001
Enter in B1 20%, B2 30%, B3 30% B4 40%

With the salary to be looked up in cell D1
=VLOOKUP)D1,$A$1:$B$4,2,1)

will return the appropriate tax rate