0

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
| improve this question | | | | |
  • 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
1

If you will always have A10 < A11 < A12, …, and B10 = A11, B11 = A12, …, and B5A10 and B5B12, 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.

| improve this answer | | | | |
1

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)

| improve this answer | | | | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.