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.
Solution
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 ")
- Alternative to Regex for large string format/replace
- Error in grepl(pattern, df): invalid regular expression
- Regex for last n characters of String in PostgreSQL query
- Extract Int from regex using pattern matching without extracting as String and then casting toInt
- vim substitution : # for a single $ and nothing to do if $$
- regular expression in R, reuse matched string in replacement
- Pattern Matcher valid number java
- sed repetition match misbehaving
- Regex to match two parts of a URL
- Regex for matching data between parenthesis BUT with a pattern to match inside
- How to add one more pattern to my street-name regex?
- Retrieve String Regex Pattern Match
- Pattern match using regex in C
- Regex Pattern Matching -a substring in words in CSV File
- How to write regular expression with format "[12,23][12,23]"?
- bash - print regex captured groups
- Extract text in two columns from a string
- Match whitespace but not newlines
- In Perl, how can I match a number N and the next N characters?
- How can I print the matched string using Perl
- How to check that string consists only from characters `A-z-—’', space and of length from 1 to 250 symbols?
- How to match tabs and spaces and exclude forward slash in bash
- Postgres Regex: extract string after the last occurence of the pattern and the end of line
- Python - Split string on first occurrence of non-allowable characters
- regex for pairs between commas with newlines Javascript
- Extract IBAN from text with Python
- Pattern match multiple values in a sentence
- stringr extraction regex not working as expected
- java regex get value after specific value
- Capture all matching patterns after a string text