lookup value between cells

[Guest]

I have a rather large spreadsheet (7500 rows) and need to be able to find the
value of one cell based on if another value is between the values in columns
B and C.
For example, if the value I need to reference is 59216, I want to have the
formula return the value in column E when column B is 59200 and column C is
59300. Is there a way to do this with LOOKUP or some function?

As always, thanks to the contirbutors who make my life much simpler with
their expertise!

 

[Guest]

I don't understand the question. How do 59200 & 59300 relate to your lookup
value of 59216?

Dave

 

[Guest]

Where does the 59216 come from? I am going to assume that it is another
cell, in this case D1

=IF(AND(D1>B1,D1<C1),E1,"No Match")

 

[Guest]

The 59216 would be entered on a different spreadsheet. If we were doing the
lookup manually, we would be scanning down the 7500 rows until we got to the
closest number less that 59216, which would be 59200. The 59300 is the top of
the range that the value in column E would be valid for. So what I am trying
to find is if X is between the values in B1 and C1, then E1 would be returned
from the formula. I hope that this clarifies my question.

 

[vezerid]

I would suggest VLOOKUP but I donot know what is the behavior of
column B (which is what matters for this solution). So, the more
powerful solution, with array formula,

=INDEX(E2:E100,MATCH(1,(B2:B100>=59216)*(C2:C100<=59216),0))

Commit with Shift+Ctrl+Enter. Replace 59216 with cell reference. Add $
$ to suit.

HTH
Kostis Vezerides

 

[Guest]

Thanks! That looks like it is going to do the trick!!