Table Array Lookup

Copper Contributor

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, I can't resolve.  The attached shows what I want returned, after a search of the Data set has been performed.

 

Can this be done in Excel?

2 Replies

In relation to my initial post, please see the attached where I have been able to return a vlaue under the 'Name' column for rows that contained a "(".  The blue column tests for a "(" in the yellow data set area.  The green column performs the lookup to return a "Name" label for cells that contained a "(".

 

How do I extend this to perform search across multiple columns as per my original post?

Hi,

 

I'd create helper column first

image.png

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)