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