获取定义范围内搜索条件的最后一列号(如果值不是唯一的) [英] 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屋！