Formula to find whether or not a number falls between ranges listed in two columns

You can use a simple Excel formula to find whether or not a number falls between a range listed in two other columns on a worksheet.


=LOOKUP(2,1/((A5>=G:G)*(A5<=H:H)),I:I)


The LOOKUP formula looks up the value given in cell A5 first in column G to see if it is greater or equal to a value there, and then in column H to see if it is less than or equal to a value there. It then returns the value listed in the last column, which is here 'I', and is listed at the end of the formula.