CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulas
Dave
Dave

Reputation: 15

Search for Value and Return All Columns In Which Value Appears

I have the following set of lists of names in a Google Sheet:

╔═══╦════════════╦══════════╦══════════╗
║   ║     A      ║     B    ║     C    ║
╠═══╬════════════╬══════════╬══════════╣
║ 1 ║ Barry      ║ Rich     ║ David    ║
╠═══╬════════════╬══════════╬══════════╣
║ 2 ║ Rich       ║ Jeff     ║ Michael  ║
╠═══╬════════════╬══════════╬══════════╣
║ 3 ║ Bruce      ║ Bruce    ║ Jeff     ║
╠═══╬════════════╬══════════╬══════════╣
║ 4 ║ Rober      ║ Barry    ║ Joel     ║
╠═══╬════════════╬══════════╬══════════╣
║ 5 ║ David      ║ Joel     ║ Howard   ║
╠═══╬════════════╬══════════╬══════════╣
║ 6 ║ Michael    ║ Howard   ║ Rich     ║
╚═══╩════════════╩══════════╩══════════╝

I'm trying to figure out a formula to use that searches for a name and returns all the names that appear in each column the name appears in. For example, if I search for Joel I want to get the entire contents of columns B and C.

Upvotes: 1

Views: 98

Answers (5)

marikamitsos
marikamitsos

Reputation: 10573

You asked for a formula that

...searches for a name and returns all the names that appear in each column the name appears in

Please use the following formula

=query(ArrayFormula(regexextract( 
                query(flatten(TRANSPOSE(ArrayFormula(column(A2:C)&"-"&A2:C))), 
                   "where Col1 matches '"&textjoin("|",1,(ArrayFormula(if(A2:C=A1,column(A2:C)&"-.*",""))))&"' ") 
                                ,"-(.*)")),  
    "where Col1 is not null ")

Return each column in whole where A1 is present

Upvotes: 1

player0
player0

Reputation: 1

or maybe:

=ARRAYFORMULA(FLATTEN(SPLIT(TRIM(REGEXREPLACE(FILTER(A:A&" "&B:B&" "&C:C, 
 REGEXMATCH(A:A&" "&B:B&" "&C:C, D10)), D10, )), " ")))

enter image description here

Upvotes: 0

kirkg13
kirkg13

Reputation: 3010

Are you looking for an output like this: enter image description here

Let us know if this helps or not.

The main formula is a bit brute force, but pretty straightforward. But it might have issues depending on how many rows or columns you might have in your data.

=TRANSPOSE(QUERY({TRANSPOSE(A1:C6)},"
             where Col1 = '" & F1 & "' 
                or Col2 = '" & F1 & "' 
                or Col3 = '" & F1 & "' 
                or Col4 = '" & F1 & "' 
                or Col5 = '" & F1 & "' 
                or Col6 = '" & F1 & "' ",0))

UPDATE:

To put all of the results into a single column, there are several options. You haven't said if you want unique values, or the names sorted, so I'll ignore those aspects, and just stack the results vertically.

One simple way is the use of an undocumented function, FLATTEN, which may be or may not be removed from Sheets at some point.

Added to my original formula, that gives:

=FLATTEN(TRANSPOSE(QUERY({TRANSPOSE(A1:C6)},"
             where Col1 = '" & F1 & "' 
                or Col2 = '" & F1 & "' 
                or Col3 = '" & F1 & "' 
                or Col4 = '" & F1 & "' 
                or Col5 = '" & F1 & "' 
                or Col6 = '" & F1 & "' ",0)))

But you may want one column stacked on top of another, not a merger of them. In that case, you could try this ugly formula, which needss to repeat the query multiple times, to check how many columns there are in the output. If you have to go this way, I recommend to either save the query results in a helper range, or perhaps submit a new more specific question:

={query({TRANSPOSE(QUERY({TRANSPOSE(A1:C6)},"
             where Col1 = '" & F1 & "' 
                or Col2 = '" & F1 & "' 
                or Col3 = '" & F1 & "' 
                or Col4 = '" & F1 & "' 
                or Col5 = '" & F1 & "' 
                or Col6 = '" & F1 & "' ",0))},"select Col1 ",0); 
 if(columns(TRANSPOSE(QUERY({TRANSPOSE(A1:C6)},"
             where Col1 = '" & F1 & "' 
                or Col2 = '" & F1 & "' 
                or Col3 = '" & F1 & "' 
                or Col4 = '" & F1 & "' 
                or Col5 = '" & F1 & "' 
                or Col6 = '" & F1 & "' ",0)))>1,
     query({TRANSPOSE(QUERY({TRANSPOSE(A1:C6)},"
             where Col1 = '" & F1 & "' 
                or Col2 = '" & F1 & "' 
                or Col3 = '" & F1 & "' 
                or Col4 = '" & F1 & "' 
                or Col5 = '" & F1 & "' 
                or Col6 = '" & F1 & "' ",0))},"select Col2 ",0),
     "");
  if(columns(TRANSPOSE(QUERY({TRANSPOSE(A1:C6)},"
             where Col1 = '" & F1 & "' 
                or Col2 = '" & F1 & "' 
                or Col3 = '" & F1 & "' 
                or Col4 = '" & F1 & "' 
                or Col5 = '" & F1 & "' 
                or Col6 = '" & F1 & "' ",0)))>2,
     query({TRANSPOSE(QUERY({TRANSPOSE(A1:C6)},"
             where Col1 = '" & F1 & "' 
                or Col2 = '" & F1 & "' 
                or Col3 = '" & F1 & "' 
                or Col4 = '" & F1 & "' 
                or Col5 = '" & F1 & "' 
                or Col6 = '" & F1 & "' ",0))},"select Col3 ",0),
     "")}

enter image description here

Upvotes: 0

basic
basic

Reputation: 11968

You can try following formula:

=IFERROR(INDEX($A:$C,0,SMALL(FILTER(FLATTEN(COLUMN($A:$C)*("Jeff"=$A:$C)),FLATTEN(COLUMN($A:$C)*("Jeff"=$A:$C))>0),COLUMN(A1))),"")

This formula autofill rows, but not columns, so copy it to the right.

enter image description here

Upvotes: 1

player0
player0

Reputation: 1

try like this:

=VLOOKUP("Joel", A:C, {2, 3}, 0)

next level:

=IFNA(VLOOKUP(A1, Sheet1:A:C, {2, 3}, 0))

next level:

=ARRAYFORMULA(IFNA(VLOOKUP(A1:A, Sheet1:A:C, {2, 3}, 0)))

if more columns:

=ARRAYFORMULA(IFNA(VLOOKUP(A1:A, Sheet1:A:C, COLUMN(B:K), 0)))

if Joel is in B column and you want to return A and C column:

=ARRAYFORMULA(IFNA(VLOOKUP(A1:A, {Sheet1!B:B, Sheet1!A:C}, {2, 4}, 0)))

Upvotes: 0

Related Questions