CODEWITHSUNDEEP
excelexcel-formula
FinDev
FinDev

Reputation: 439

How to find index of cell value in range without using an embedded IF Statement

Please see the photo below. I'd like to find which Number in row 3, is affiliated with the first number higher than the number in cell B1. As you can see below, the first number higher than 11.2 is 11.59. The desired output is the number affiliated with 11.59 in row 3, which is 4.

Is there a way to do this without a 10 times embedded IF statement and still use inbuilt Excel formulas. I'd prefer not to use VBA for this one as well.

enter image description here

Upvotes: 0

Views: 1001

Answers (1)

Isolated
Isolated

Reputation: 6454

Here's the formula for cell E1:

    =OFFSET(INDEX(B5:K5,MATCH(TRUE,B5:K5>B1,0)),-2,0)

This is an array formula (ctrl+shift+enter).

enter image description here

Upvotes: 1

Related Questions