Forum Discussion
Table Array Lookup
Hi, I have been trying to find a way to return a name when a row of data contains a "(". Have been succesful when I only have one column of data, in this example, column C. Once I move beyond this...
Hi,
I'd create helper column first
as
=SUMPRODUCT(LEN(C2:G2)-LEN(SUBSTITUTE(C2:G2,"(","")))
and after that in J2 and down
=IFERROR(INDEX($A$2:$A$5,AGGREGATE(15,6,1/($H$2:$H$5=1)*(ROW($A$2:$A$5)-1),ROWS($J$1:$J2)-1)),"")
similar next column.
Same could be done with Power Query (first sheet in attached)