查找两个给定值之间的范围内的值 [英] Looking up a value in a range that is between two given values
问题描述
我有以下公式:
=INDEX(SPEC!$C$5:$C$650,MATCH(B3,SPEC!$F$5:$G$5,0))
它工作正常,但 $ F $ 5
的值为1, $ G $ 5
的值为100.因此,如果我之间输入任何内容: 2-99然后它不工作。
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.
有没有办法使用< =
和> =
在某处可以看到2到99之间的数字,因为我当前的公式只是看着我提到的那些单元格,而不是之间的所有内容。
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.
推荐答案
没有看到您的确切工作簿,我不得不创建一个可能不完全复制您的虚拟版本(见下文)。
Without seeing your exact workbook I have had to create a dummy version which may not exactly replicate yours (see below).
基于只有一个值在 $ F $ 5
和 $ G $ 5之间
在您的价值范围内(在我的情况下 D5:D650
),以下应该可以工作。
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))) code>。
=INDEX(C5:C650, SUMPRODUCT(--(D5:D650<=G5),--(D5:D650>=F5), ROW(A1:A646)))
.
说明:
D5:D650 <= G5 = {FALSE,FALSE,FALSE,FALSE,TRUE,FALSE ...}
使用两个减号在此之前,将True的结果转换为值1,将False的结果转换为值0。
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."
所以在案例 D5:D650 <= G5 = {FALSE,FALSE,FALSE,FALSE,TRUE,FALSE ...}
将其转换为 {0 ,0,0,0,1,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...}
下一个条件 - (D5: D650> = F5)
等于 {1,1,1,1, 1,1 ...}
最后, ROW(A1:A646)
元素,只需创建一个数组 {1,2,3,4,5,6 ...}
Finally the ROW(A1:A646)
element, just creates an array {1, 2, 3, 4, 5, 6...}
SUMPRODUCT然后用于查找数组的乘积的总和,例如对于阵列的第一个元素,产品为0 * 1 * 1 = 0,但对于fift元素(符合您的标准的元素),产品为1 * 1 * 5,这将为您提供行号。
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然后给您您正在寻找的答案。
INDEX then gives you the answer you are looking for.
这篇关于查找两个给定值之间的范围内的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!