Forum Discussion

Pat_J_Hartley's avatar
Pat_J_Hartley
Copper Contributor
Mar 05, 2019

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, 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?

excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Mar 05, 2019

    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)

  • Pat_J_Hartley's avatar
    Pat_J_Hartley
    Copper Contributor
    Mar 05, 2019

    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?

Resources