使用INDEX(MATCH())返回单元格的第n个值 [英] Using INDEX(MATCH()) to return the n'th value of a cell
问题描述
我有一个包含2列的简单工作表
I have a simple worksheet with 2 columns
我想得到所有的结果(列H)(我只能得到第一次出现,我想知道我是否可以得到其他人)包含来自单元格G1的值,是否可能没有宏?任何想法都可以...任何想法?
I want to get all the results(on column "H") (I can get only the first occurrence,I want to know if I can get the others) that contains the value from cell G1, is that possible without a macro ? any way of doing it would be appreciated...Any ideas?
推荐答案
您可以使用 ROW )
和 SMALL()
获得那些而不是 MATCH()
,因为这总是得到第一场比赛。
You can use ROW()
and SMALL()
to get those instead of MATCH()
since this always gets the first match.
=IFERROR(INDEX($C$4:$C$7,SMALL(IF($D$4:$D$7=$G$1,ROW($D$4:$D$7)-(ROW()-1)),ROWS($D$4:D4))),"Null")
所以,如果数组 $ D $ 4:$ D $ 7 = $ G $ 1
返回true(即该值等于G1中的值),您将获得这些值的行号,在这种情况下,您将获得4和6.所有其他将返回 False
。
So, if the array $D$4:$D$7=$G$1
returns true (i.e. the value equals that in G1), you will get the row numbers of these values, in this case, you will get 4 and 6. All the other will return False
.
在使用 - (ROW() - 1)
进行一些处理后,4和6变为1和3.这两个值将被馈送到 INDEX
。
After some processing with -(ROW()-1)
, the 4 and 6 become 1 and 3. those two values are what will be fed to the INDEX
.
SMALL ()$($ D $ 4:D4)从$ code> ROWS($ D $ 4:D4)中,选择最小的(从$ 1开始)($ code> 1
),当您拖动公式时, ROWS
成为 ROWS($ D $ 4:D5)
其中给出2,而 SMALL
最终取第二个最小值, 3。
SMALL()
then picks the smallest, starting with the 1st (you get 1
from ROWS($D$4:D4)
) and when you drag the formula down, the ROWS
become ROWS($D$4:D5)
which gives 2, and SMALL
ends up taking the 2nd smallest value, which is 3.
编辑:忘记提了你必须要数组输入上面的公式。为此,键入公式(如有必要再次编辑公式)而不是 Ctrl + Shift + 输入 kbd>单独输入。
Forgot to mention. You have to array enter the above formula. To do this, type the combination keys of Ctrl+Shift+Enter after typing the formula (edit the formula again if necessary) instead of Enter alone.
这篇关于使用INDEX(MATCH())返回单元格的第n个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!