Match Formula to Check if Cell Value is Within a Numeric Range Table

**dieseldogpi** · 08-16-2012, 11:33 AM

OK, I am trying to create a formula to check what quantity break a cell value falls in. I originally tried using the Min and Max function to match the Low and High value of the break, but I could not get it to work. Now I am using the index and match function. For some reason the match function is returning the wrong row. I want it to return the row that the quantity is less than the high value. For some reason my results are not consistent. What am I doing wrong. File attached. Thanks for your help, feel like I was SO close on this one.

**NBVC** · 08-16-2012, 11:34 AM

Try:

=LOOKUP(B6,$F$7:$H$30)

**dieseldogpi** · 08-16-2012, 11:42 AM

Wow that works...but how? I didn't think just referencing the cell, then the table array would do that. I was going to try that originally, it just didn't seem to do what I wanted. Either way, totally awesome, thanks again! I will mark this as solved.

**NBVC** · 08-16-2012, 12:04 PM

if looks for the last entry that is less than or equal to the lookup value, B6 in the first column of array F7:H30 and returns the corresponding value from last column in F7:H30.