这个解决方案适用于到目前为止讨论的所有情况
(如果有任何不适用的情况,请告诉我,我会尽力涵盖)。我将其作为一个独立的答案发布,因为在先前的答案中应用的公式仅适用于其中所述的条件,因此它们将对具有这些特定场景的用户非常有用,因此他们不需要应用这些冗长的公式。
该公式假定数据位于B6:E30
(为了确保它可以应用于任何源范围位置)。
该公式使用Index\Match函数,是一个Formula Array。
FormulaArrays是通过同时按下[Ctrl]
+ [Shift]
+ [Enter]
输入的,如果正确输入,您将看到该公式周围有{
和}
。
语法:
=IFERROR(INDEX(DataRng,
MATCH(Value1,NamesRng,0)
+IFERROR(MATCH(Value2,INDEX(NamesRng,
1+MATCH(Value1,NamesRng,0))
:INDEX(NamesRng, IFERROR(MATCH(Value1,NamesRng,0)
+MATCH("#",IF((INDEX(Col1Rng,1+MATCH(Value1,NamesRng,0))
:INDEX(Col1Rng,ROWS(NamesRng)))="","#","!"),0),
ROWS(NamesRng))),0),NA()),MATCH(ValCol,DataHdr,0)),"")
参数:
假设数据位于B6:E30。
Value1
= Name
,在数据中需要查找的名称,如George、Scott等。
Value2
= Detail
,在数据中需要查找的细节信息,如Detail1、Detalle2等。
ValCol
= Column
,在数据中需要查找的列,如Column1、Column2等。
DataRng
= $B$6:$E$30
DataHdr
= $B$6:$E$6
NamesRng
= $B$6:$B$30
Col1Rng
= $C$6:$C$30
第一次匹配:获取名称的位置:
MATCH(Value1,NamesRng,0)
第二个匹配:检索与姓名对应的详细信息的结束位置,该位置由列C
中的空值或数据范围的末尾确定:
MATCH("#",IF((INDEX(Col1Rng, 1 + 1stMATCH)
:INDEX(Col1Rng,ROWS(NamesRng)))="","#","!"),0),
使用第一个和第二个匹配函数的名称详细信息,构建范围(vRange)。如果第二个匹配返回错误,则使用数据范围的最后一行。
INDEX(NamesRng, 1 + 1stMATCH )
:INDEX(NamesRng, IFERROR( 1stMATCH + 2ndMATCH, ROWS(NamesRng)))
第三个匹配: 检索 vRange
中 Detail
的位置。如果组合不存在,则返回 #NA
。
IFERROR(MATCH(Value2, vRange,0), NA())
将第一个和第三个匹配函数的结果相加,可以获得Name
`Detail`组合的行索引,如果没有找到则返回#NA
。
使用数据标题进行匹配可以获得列索引。
然后应用INDEX函数于数据范围,返回Name\Detail\Column
组合的值。
如果未找到Name\Detail
组合,则返回空白。
=IFERROR( INDEX( DataRng, 1stMATCH + 3rdMATCH, MATCH(Column,DataHdr,0)),"")
将结果位于H6:L37,输入此数组公式到J8,然后复制到K8:L37和J9:L37:
=IFERROR( INDEX($B$6:$E$30,
MATCH($H8,$B$6:$B$30,0)
+IFERROR( MATCH($I8, INDEX($B$6:$B$30,
1+MATCH($H8,$B$6:$B$30,0))
:INDEX($B$6:$B$30, IFERROR(MATCH($H8,$B$6:$B$30,0)
+MATCH("#", IF((INDEX($C$6:$C$30,1+MATCH($H8,$B$6:$B$30,0))
:INDEX($C$6:$C$30,ROWS($B$6:$B$30)))="","#","!"),0),
ROWS($B$6:$B$30))),0),NA()),
MATCH(J$6,$B$6:$E$6,0)), "")
![enter image description here](https://istack.dev59.com/CI7tz.webp)