Compare column with cell

[jamberfield]

I currently have this setup in a worksheet
A B C D F
ON01 1100 1150 990 ???
ON02 1200 1300
ON03 900 1000
ON04 300 800

Now what i am trying to accomplish is this.

Column cell B is the minimum number for Column cell A
Column cell C is the max number for Column cell A

I want D1 to check which column row it is between(D1:C1,D2:C2,D3:C3...etc), and if D1 is greater then Bx and Less then Cx, I want F1 to display what ever row Ax is in that D1 value is between.

The example above should return ON03 to F1 becasue D1 is 990 and it matches the range which is between B3:C3:

So IF D1 > Bx And D1<Cx Display Ax in F1

[Palmetto]

If you don't mind using a helper column, take a look at the attached and see if this works for you.

Formula in helper cell and copied down the column

=AND($D$1>=B1,$D$1<=C1)

Formula in F1

=INDEX(A1:A4,MATCH(TRUE,G1:G4,0))

[DonkeyOte]

For a single cell approach - assuming there is only one valid match:

F1: =LOOKUP(2,1/((B1:B4<=D1)*(C1:C4>=D1)),A1:A4)

If there are multiple matches the above would return the last match, to return the first:

F1: =INDEX(A1:A4,MATCH(1,INDEX((B1:B4<=D1)*(C1:C4>=D1),0),0))

Pending volume of data points Palmetto's suggestion of using helpers is a sensible one - neither of the above suggestions are what you would term "efficient" with big data sets.