Select a value from range

Question

I'm trying to put the category number (1..5) into E3 based on the value in F3 being in between the From (B) and To (C) range. For instance in the example below F3 contains 19.3 which is between 12.5 and 20.4 so it should put a "3" into E3.

I've tried Query (error, "circular dependency" if I include F3 in the data, or "F3 not found" if I don't - moving F3 closer to the category data isn't an option for me here).

And I've tried a basic if statement which sheets refuses to evaluate:

=if(F3>=$B$2 and F3<=$c$2,a2,if(F3>=$B$3 and F3<=$c$3,a3,if(F3>=$B$4 and F3<=$c$4,a4,if(F3>=$B$5 and F3<=$c$5,a5,if(F3>=$B$6 AND F3<=$c$6,a6,"")))))    

Any ideas?

Answer 1

In your case I think VLOOKUP is a better fit.

Actually you can use either of the following two formulas:

Either #1:

=VLOOKUP(F3,{B2:C6,A2:A6},3,1)

or #2:

=VLOOKUP(F3,{B2:B6,A2:A6},2,1)

As you understand from the second formula we can completely omit column C.

Answer 2

I used scripting to solve it, though I've marked the simpler solution above (which I read later) as the correct answer. Here's my solution using google app script:

var hcpBounds = [-99, 5.49, 12.49, 20.49, 28.49, 36.49];

function GolfCategory(hcp) {                   

    for (var i = hcpBounds.length-1; i >= 0; i--) {
      if (hcp > hcpBounds[i]) {
        return i +1;
      }
    }

    return hcpBounds.length +1;
}

Then in the spreadsheet I set E3 to

=GolfCategory(F3)