找到你要的答案

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

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))

它运作良好,但$ F $ 5有1和$ G $ 5的价值为100。所以,如果我型任何之间即:2-99然后它不工作。

有一种方法,使用<;=和>;=的地方让它看起来也在2和99之间的数字作为我的电流公式只看我提到的并不是一切之间的那些细胞。

answer1: 回答1:

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.

没有看到你的确切工作簿,我不得不创建一个虚拟版本,可能不完全复制你的(见下文)。

在此基础上,将只有一个值,介于$ 5美元5美元你G值的范围(在我的情况下,D5:D650),下面的工作。

=指数(C5:C650,SUMPRODUCT(--(D5:D650 <;= G5),--(D5:D650 >;= F5)、行(A1:a646)))。

解释:

D5:D650 <;= G5 = {假,假,假,假,真的,假的…}

在此之前使用两个减号,将“true”的结果转化为“1”的值,并将“false”的结果转化为“0”的值。

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

下一个标准--(D5:D650 >;= F5)等同于{ 1,1,1,1,1,1,…}

最后一行(A1:a646)元素,只是创建一个数组{ 1,2,3,4,5,6,…}

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

指数然后给你你正在寻找的答案。

excel  excel-formula