Lookups and ifs!

  • Thread starter Thread starter Annie
  • Start date Start date
A

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
 
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})
 
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
 
Back
Top