# How do I determine if a value is between range of values specified in two columns and return another value from the row where condition is true?

I have a value that I enter in cell B5. I need a value returned from column C and the corresponding row where the value is between minimum values in A10:A12 and maximum values in B10:B12. In the example below the value in C11 (0.025) is returned:

B5 = 11

`````` A    B     C
0   10   0.0023
10   20   0.025
20   30   0.11
``````
• Do you mean for `B12` to be 10? It looks like it should be 30, or at least something > 20. – Scott Nov 4 '13 at 23:20

If you will always have `A10` < `A11` < `A12`, …, and `B10` = `A11`, `B11` = `A12`, …, and `B5``A10` and `B5``B12`, then `VLOOKUP` gives a quick answer:

``````=VLOOKUP(B5, \$A\$10:\$C\$12, 3)
``````

Note that this actually ignores `B10`:`B12`, so any `B5` value ≥ 20 will give you 0.11 (the specified result for 10-20).  If you want `B12` honored as an upper limit, add a row 13 with `A13` = `=B12`, `B13` is anything (or nothing), and `C13` = `#N/A`, and change the `12` in the `VLOOKUP` formula to `13`.

Assuming the ranges don't have gaps (as looks to be the case) then it's sufficient to use just the lower bounds in col A, i.e.

`=LOOKUP(B5,A10:A12,C10:C12)`

• Beat you by 18 seconds! :-) – Scott Nov 4 '13 at 23:51
• Nah! it was only 17............... – barry houdini Nov 4 '13 at 23:55