# Q：在两个给定值之间的范围内查找值

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.

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

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

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.

=指数（C5：C650，SUMPRODUCT（--（D5：D650 <；= G5），--（D5：D650 >；= F5）、行（A1：a646）））。

D5：D650 <；= G5 = {假，假，假，假，真的，假的…}

So in the case of D5：D650 <；= G5 = {假，假，假，假，真的，假的…} it transforms this to `{0, 0, 0, 0, 1, 0...}`

SUMPRODUCT进行阵列的产品是0×1×1＝0但对于50元-一个符合你的标准产品的第一个元素的数组，如发现产品的总和是1×1×5，这给你的行数。

excel  excel-formula