top of page Search

# Excel Array Formula to find when value falls in range specified in two columns

This is an Excel array formula that can be used to return a specified value when a given value falls between a number range specified in two other columns. So in this example, we're checking to see if the value in column G falls between the ranges listed in columns B and C, and if it does return the corresponding value in column A.

{=INDEX(A:A,MATCH(1,(G2>=B:B)*(G2<=Sheet1!C:C),0))}

This is an array formula, so when entering it, you need to press CTRL + SHIFT + ENTER, so that the formula is surrounded by 'brace' brackets {}. So we can see that when a weight of 158 is given for Jake LaMotta, the formula correctly identifies that he's a middleweight, which is a boxer weighing between 155 and 160 pounds. The 119 to 122 Super bantamweight class is omitted on this spreadsheet, so a boxer in this class, Hozumi Hasegawa at 120 is not assigned a weight class by the formula. This is the advantage that the formula has over a VLOOKUP formula which can only be used to find the weight class which is nearest to the given weight. bottom of page