获取定义范围内搜索条件的最后一列号(如果值不是唯一的) [英] Get the last column number of a search criteria in a defined range (if values are not unique)
问题描述
我有以下Excel电子表格:
I have the following Excel spreadsheet:
A B C D E F G H I
1 Search Criteria: Prod.B
2 Column: 2
3 Prod.A Prod.B Prod.B Prod.C Prod.C Prod.D
4
5
当前,我在Cell C2
中使用以下公式在Range D3:I3
中的Cell C1
中获取搜索条件的列.
Currently I use the following formula in Cell C2
to get the column of the search criteria in Cell C1
in the Range D3:I3
.
=MATCH(C1,D3:H3,0)
所有这些都完美地工作.
All this works perfectly.
但是,如您在上面的公式中所看到的,我获得了出现搜索条件的第一列编号.
However, as you can see with the formula above I get back the first column number in which the search criteria appears.
要获取显示搜索条件的最后列号,我需要在公式中进行哪些更改? (在上面的示例中为3)
What do I need to change in the formular to get the last column number in which the search criteria appears? (In the example above it would be 3)
推荐答案
=MAX(IF(D3:I3=C1,COLUMN(D3:I3)-3))
或者:
=MATCH(2,1/(D3:I3=C1))
通过 Ctrl Shift Enter
或者:
Alternatively:
=LOOKUP(2,1/(D3:I3=C1),COLUMN(D3:I3)-3)
它仍然应该是一个数组公式,但是您不必这样输入.
It should still be an array formula but you don't have to enter it as such.
这篇关于获取定义范围内搜索条件的最后一列号(如果值不是唯一的)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!