这个公式可以返回一个二维数组中给定值的行列号。
=LET(
array, B2:D4,
findvalues, C7,
arrayrows, ROWS(array),
arraycols, COLUMNS(array),
rowindex, SEQUENCE(arrayrows*arraycols,,1,1/arraycols),
colindex, MOD(SEQUENCE(arrayrows*arraycols,,0),arraycols)+1,
flatarray, INDEX(array,rowindex,colindex),
valueflatindex, MATCH(findvalues,flatarray,0),
valuerow, ROUNDUP(valueflatindex/arraycols,0),
valuecol, MOD(valueflatindex-1,arraycols)+1,
absvaluerow, MIN(ROW(array))+valuerow-1,
absvaluecol, MIN(COLUMN(array))+valuecol-1,
CHOOSE({1,2},absvaluerow,absvaluecol)
)
A B C D E
1
2 John Matt Pete
3 Sara Bret Chad
4 Lila Maya Cami
5
6
7 find: Chad
8 formula: 3 4
更精确地说,此公式逐行扫描给定的数组,并返回给定值第一次出现的地址。
如果您需要相对于数组左上角单元格的行列号,则在
CHOOSE(...)
中,使用
valuerow/valuecol
,而不是
absvaluerow/absvaluecol
。
如果您想要将值以逗号分隔并放在一个单元格中,而不是使用
CHOOSE(...)
,则使用
absvaluerow & "," & absvaluecol
。
如果您的Excel版本不支持最新的函数,例如
LET,则如果您重写它使其不使用LET变量,则该公式仍应起作用。
查找多个值:
您还可以使用此公式在数组中查找多个值,如
this thread中所述。