Home

Table Array Lookup

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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies