Forum Discussion
Determine where a value fits between a table of ranges and pull an associated cell from the row
An Excel table of number ranges (e.g. 0 - 1, 1.1-2, 2.1-3) and an associated field to retrieve when the value being compared against fits in the range. Value passed in is 0.18. This should result in the value fitting in to row 2 (0.17-0.52) and the SKU to be returned is 2234567. I have an i=IF(AND(A15>=MIN(B8,C8),A15<=MAX(B8,C8)),"YES","NO") working on a single row, but struggling with how to get it to look through all the ranges.
| Lower Range | Upper Range | SKU |
| 0.00 | 0.16 | 1234567 |
| 0.17 | 0.52 | 2234567 |
| 0.53 | 1.24 | 3234567 |
| 1.25 | 2.43 | 4234567 |
| 2.44 | 4.98 | 5234567 |
| 4.99 | 8.27 | 6234567 |
| 8.28 | 10.04 | 7234567 |
=INDEX($C$2:$C$8,MATCH(1,($A$2:$A$8<=E1)*($B$2:$B$8>=E1),0))
With this formula you can enter the search value in cell E1 and the formula dynamically returns the SKU.
- Thank you very much, works great!
