CODEWITHSUNDEEP
excelexcel-formula
Jonok
Jonok

Reputation: 35

Looking up a value in a range that is between two given values

I have the following formula: 

=INDEX(SPEC!$C$5:$C$650,MATCH(B3,SPEC!$F$5:$G$5,0))

It works fine but $F$5 has a value of 1 and $G$5 has a value of 100. So, if I type anything in between ie: 2-99 then it doesn't work.

Is there a way of using <= and >= somewhere to get it to also look at the numbers between 2 and 99 as my current formula only looks at those cells I mentioned and not everything in between.

Upvotes: 1

Views: 8157

Answers (1)

newishuser
newishuser

Reputation: 610

Without seeing your exact workbook I have had to create a dummy version which may not exactly replicate yours (see below).

Screenshot of my example workbook

On the basis that there will only be one value that falls between $F$5 and $G$5 in your values range (in my case D5:D650), the following should work.

=INDEX(C5:C650, SUMPRODUCT(--(D5:D650<=G5),--(D5:D650>=F5), ROW(A1:A646))).

Explanation:

D5:D650<=G5 = {FALSE, FALSE, FALSE, FALSE, TRUE, FALSE...}

Using two minus signs before this, transforms the result of “True” into a value of “1” and a result of “False” into a value of “0.”

So in the case of D5:D650<=G5 = {FALSE, FALSE, FALSE, FALSE, TRUE, FALSE...} it transforms this to {0, 0, 0, 0, 1, 0...}

The next criteria --(D5:D650>=F5) equates to {1, 1, 1, 1, 1, 1...}

Finally the ROW(A1:A646) element, just creates an array {1, 2, 3, 4, 5, 6...}

SUMPRODUCT is then used to find the sum of the product of the arrays e.g. for the first element of the arrays the product is 0*1*1=0 but for the fift element - the one that matches your criteria - the product is 1*1*5, which gives you your row number.

INDEX then gives you the answer you are looking for.

Upvotes: 1

Related Questions