Rechercher une valeur et renvoyer toutes les colonnes dans lesquelles la valeur apparaît

essayez comme ceci:

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

niveau suivant:

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

niveau suivant:

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

si plus de colonnes:

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

si Joel est dans la colonne B et que vous voulez retourner la colonne A et C:

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

ou peut-être:

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

Vous pouvez essayer la formule suivante:

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

Cette formule remplit automatiquement les lignes, mais pas les colonnes, alors copiez-la à droite.

Cherchez-vous une sortie comme celle-ci:

Faites-nous savoir si cela aide ou non.

La formule principale est un peu la force brute, mais assez simple. Mais cela peut avoir des problèmes en fonction du nombre de lignes ou de colonnes que vous pourriez avoir dans vos données.

={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),
     "")}

MISE À JOUR:

Pour mettre tous les résultats dans une seule colonne, il existe plusieurs options. Vous n'avez pas dit si vous voulez des valeurs uniques ou des noms triés, je vais donc ignorer ces aspects et empiler les résultats verticalement.

Un moyen simple consiste à utiliser une fonction non documentée, FLATTEN , qui peut être ou non supprimée de Sheets à un moment donné.

Ajouté à ma formule originale, cela donne:

=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)))

Mais vous voudrez peut-être une colonne empilée sur une autre, pas une fusion. Dans ce cas, vous pouvez essayer cette formule laide, qui nécessite de répéter la requête plusieurs fois, pour vérifier le nombre de colonnes dans la sortie. Si vous devez suivre cette voie, je vous recommande soit d'enregistrer les résultats de la requête dans une plage d'aide, soit de soumettre une nouvelle question plus spécifique:

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

Vous avez demandé une formule qui

... recherche un nom et renvoie tous les noms qui apparaissent dans chaque colonne dans laquelle le nom apparaît

Veuillez utiliser la formule suivante

=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 ")