基于数组中的条件查找列标题

huangapple go评论92阅读模式
英文:

Find Column Header based on criteria from an array

问题

我想根据在数组中找到的条件返回标头的值

示例:如果我在单元格I2中放置C4,C4将在左侧表格的D4单元格中找到,因此它应该返回值Header 4,因为它是该列的标头。

英文:

I want to return the value of the header based on criteria that can be found in the array

Example: If I put C4 on cell I2, C4 is found at cell D4 on the left table therefore it should return value Header 4 since it is the header of that column

基于数组中的条件查找列标题

答案1

得分: 6

以下是翻译好的部分:


• Formula used in cell J2

=CONCAT(REPT(A1:F1,N(I2=A2:F5)))

Or,

• Formula used in cell J3

=FILTER(A1:F1,BYCOL(A2:F5=I2,LAMBDA(x,OR(x))))

Or,

• Formula used in cell J4

="Header "&AGGREGATE(15,6,COLUMN($A$1:$F$1)/($A$2:$F$5=I2),1)

英文:

Try:

基于数组中的条件查找列标题


• Formula used in cell J2

=CONCAT(REPT(A1:F1,N(I2=A2:F5)))

Or,

• Formula used in cell J3

=FILTER(A1:F1,BYCOL(A2:F5=I2,LAMBDA(x,OR(x))))

Or,

• Formula used in cell J4

="Header "&AGGREGATE(15,6,COLUMN($A$1:$F$1)/($A$2:$F$5=I2),1)

答案2

得分: 3

匹配列,返回标题

=LET(data,A1:F5,c,I2,e,"",
    h,TAKE(data,1),d,TOCOL(DROP(data,1)),
IFERROR(INDEX(h,,MOD((XMATCH(c,d)-1),COLUMNS(h))+1),e))
英文:

Match Column, Return Header

=LET(data,A1:F5,c,I2,e,"",
    h,TAKE(data,1),d,TOCOL(DROP(data,1)),
IFERROR(INDEX(h,,MOD((XMATCH(c,d)-1),COLUMNS(h))+1),e))

基于数组中的条件查找列标题

huangapple
  • 本文由 发表于 2023年5月30日 03:20:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/76359879.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定