Complex IF Statements - Assistance Needed!

[JEason93]

Hi all, I am struggling to find the correct IF statements for the spreadsheet located in the below dropbox link. The issues I am trying to overcome are:

1. Column H (Decrease) - I would like this cell to equal one of the decrease values in cells N4 - N9 based on if the value in Column J (Revised Handicap) is within one of the ranges entered in columns L (Handicap Range - Min) and M (Handicap Range - Max)
2. Column J (Revised Handicap) - I require this cell to multiple the value in Column G (Net Score) by either the value in Column H (Decrease) or Column I (Increase) based on whether the value in Column G is either positive or negative (Positive - multiple by Column H (Decrease), Negative - multiple by Column I (Increase))

https://www.dropbox.com/sh/gjmqu978d86f9rz/AADxqyJ67lYzuB-h69hPIydxa?dl=0

Any assistance that is provided would be greatly appreciated!

 

[Special-K99]

1. in H4
=IFERROR(LOOKUP(2,1/(J4>=L$4:L$9)/(J4<=M$4:M$9),N$4:N$9),"")

2. You cant have this.

The second requirement says Column J could be based on the value in column H.
But in the first requirement column H is based on column J.

You'll end up with a circular reference.

Also you haven't specified what G should be multiplied by if column G equals zero.