Forum Discussion
Within a table - Retrieve a value in a row when a provided number is between an lower/upper pair
I have a table of lower/upper range value pairs. I need to determine if a number fits in the range. If yes, retrieve the SKU value from the same row. It must search all rows in the table.
I have the formula to determine if a value fits in the range, but only on a single row. I am struggling with which function to use to scan the table entries (XLOOKUP, VLOOKUP, HLOOKUP).
=IF(AND($B$4>=MIN($B8,$C8),$B$4<=MAX($B8,$C8)),$D8,"NO RANGE FOUND")
=INDEX($D$7:$D$14,MATCH(1,($B$4>=$B$7:$B$14)*($B$4<=$C$7:$C$14),0))
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.
- Thank you very much Oliver. I went through every function last night with nothing obvious for me. I have seen this combination of INDEX and MATCH before, but being a novice, I did not see it. Thanks again. Very big help!
