Formula question regarding "if" and "lookup"

[SHC2014]

Hi All

Am new to using formulas in excel (other than basic stuff) and as mentioned in my intro thread I did join tonight to seek some assistance/advice in relation to my study.

I am currently working on an assignment and have spent about 3hrs trying to find an alternative and just can't seem to get my head around it. Now, I HAVE answered the question and the formula I wrote DID/DOES work however, it's sooooooooooooooooooooo very long. And after working that all out & getting it to work I re-read the question and thought "hmmmm are they trying to give us a hint here?"

The question reads: Add a formula to cells F11:F17 that will look up and calculate the tax amount payable and in cells G11:G17 add a formula to calculate net wages (this 2nd part I've done no issues) So I have GROSS WAGES (E11), TAX (F11), NET WAGES (G11) and 6 rows of employees to copy the formula to. Off to the side there is a table of tax rates I12 has a value of $250 and J12 0%, I13 has a value of $500 and J13 5% and so on. The formula I wrote is below and does work on first row and subsequent rows when copied down:

=IF($E11<$I$13,$E11*$J$12,IF(AND($E11>=$I$13,$E11<$I$14),$E11*J$13,IF(AND($E11>=$I$14,$E11<$I$15),$E11*$J$14,IF(AND($E11>=$I$15,$E11<$I$16),$E11*$J$15,IF(AND($E11>=$I$16,$E11<$I$17),$E11*$J$16,IF(AND($E11>=$I$17,$E11<$I$18),$E11*$J$17,IF($E11>=$I$18,$E11*$J$18)))))))


IS there a shorter way of writing this?? With the wording "look up" in the question I've been trying to work out how to use the LOOKUP function and can't do it.
I'd be grateful for any and all advice ...
Thanks
Sharen

[AliGW]

VLOOKUP would be easy enough. You need to set up a table areay with the lookup values in the first column and the return values in the second, and reference this in the formula. VLOOKUP in the Help file shows you how.

[SHC2014]

Thanks for your comment AliGW ... I'm using Excel 2013, have only ever dealt with basic basic basic formulas before 2 days ago and have been through the HELP file in relation to the LOOKUP & VLOOKUP functions and I don't understand how it works.