# Determine where a value fits between a table of ranges and pull an associated cell from the row

Brass Contributor

# 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
2 Replies

# Re: Determine where a value fits between a table of ranges and pull an associated cell from the row

=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.

# Re: Determine where a value fits between a table of ranges and pull an associated cell from the row

Thank you very much, works great!