Find Column Header based on criteria from an array

Question

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

Have a look at these solutions for multiple criteria [here](https://stackoverflow.com/q/76644962). — VBasic2008, Jul 08 '23 at 23:49

score 6 · Answer 1 · answered May 29 '23 at 19:26

6

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)

answered May 29 '23 at 19:26

Mayukh Bhattacharya

12,541
5
21
32

For **3rd One** : You can wrap within `INDEX()` if the header is always different --> `=IFERROR(INDEX(A1:F1,AGGREGATE(15,6,COLUMN($A$1:$F$1)/($A$2:$F$5=I2),1)),"")` – Mayukh Bhattacharya May 29 '23 at 19:54
1

Nice one on the CONCAT/REPT – P.b May 30 '23 at 19:34

score 3 · Answer 2 · answered May 29 '23 at 20:42

3

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))

answered May 29 '23 at 20:42

VBasic2008

44,888
5
17
28

Very smart solution! – David Leal Jul 09 '23 at 03:32

Find Column Header based on criteria from an array

2 Answers2

Match Column, Return Header

Linked