Excel INDEX MATCH vérifiant plusieurs colonnes

Vous avez un certain nombre de cas différents. Considérons un cas :

Quelque part dans les colonnes A par le biais de E il existe une seule et unique cellule contenant 13, renvoyer le contenu de la cellule dans la colonne F dans la même rangée.

Nous utiliserons une colonne "auxiliaire". Dans G1 Entrez :

=COUNTIF(A1:E1,13)

et le copier vers le bas. Cela nous permet d'identifier la rangée :

Nous pouvons maintenant utiliser MATCH()/INDEX() :

Choisis une cellule et entre :

=INDEX(F:F,MATCH(1,G:G,0))

Si les "règles" changent et qu'il peut y avoir plus d'un 13 dans une rangée ou plusieurs rangées contenant un 13, nous modifierons la colonne des aides.

EDIT#1 :

Sur la base de votre mise à jour, la première étape serait de retirer le code dur 13 des formules de la colonne "aide" et la placer dans sa propre cellule, (dire H1 ) . Vous pouvez alors exécuter différents cas en changeant simplement une seule cellule.

Si vous avez un grand nombre de cas dans une table, vous pouvez créer une macro pour configurer chaque cas. (mise à jour H1 ) et enregistrez les résultats.

Pour une seule formule en H1 :

=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))

Comme il s'agit d'une formule de tableau, nous devons limiter les références à la taille de l'ensemble de données. Toutes les INDEX(E:E,MATCH("ZZZ",F:F)) faire ça. Elle renvoie la dernière ligne de la colonne F qui contient du texte. Elle la définit ensuite comme la dernière ligne à itérer.

La méthode de @GaryStudent évite les formules Array et peut être la méthode nécessaire. Plus l'ensemble de données et le nombre de formules augmentent, plus le temps de calcul est long. Jusqu'à, à un moment donné, faire planter Excel. Habituellement, cela ne prend que quelques milliers de minutes, mais je tiens à lancer l'alerte.

EDIT

Pour éviter d'utiliser les formules Array tout en restant une seule formule :

=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),IFERROR(MATCH($H1,E:E,0),1050000))),"")

Ceci est basé sur la réponse de l'OP, j'ai juste combiné cette méthode en une seule formule.

Cette formule ignore les entrées doubles et renvoie la première ligne dans laquelle le numéro est trouvé.

Et comme il ne s'agit pas d'un tableau, les références en pleine colonne ne nuisent pas aux temps de calcul.

Une méthode différente serait basée sur une table auxiliaire, qui représente la façon dont cela "devrait" être structuré en premier lieu. Cela éviterait les équations monstres qui sont ennuyeuses à déboguer et à modifier par la suite, et cela permet de résoudre proprement un nombre variable de colonnes, contrairement à l'idée d'avoir 5 colonnes de recherche.

Si ce qui précède est dans la feuille 1, ajoutez une feuille 2. Sur cette feuille, placez quatre colonnes : Row, Column, ID, Name.

Formule en Row devrait être (en psuedo code, "Last" signifie "pour la ligne ci-dessus dans la feuille 2")

=IF(Column = 1, Last row + 1 , Last row)

Formule en Column :

=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)

Formule en ID y Name :

=INDEX(StartTable, Row, Column)    
=INDEX(NameColumn, Row, 1)

Ensuite, vous le remplissez (en gros jusqu'à ce que row >nombre de lignes dans le tableau original).

Enfin, vous utilisez la nouvelle table avec un vlookup ou un index/match ordinaire.

PRO : Formules beaucoup plus simples, plus faciles à utiliser et à comprendre.

CONS : Besoin d'une table supplémentaire, doit maintenir la longueur de la table. En ce qui concerne les performances, il y a un risque, car cela nécessite un seul thread pour toute la "chaîne" de valeurs.

De plus, si quelques lignes d'erreur sont acceptables, le code peut être un peu plus simple et peut-être plus performant, nous pouvons alors supposer que le nombre de colonnes est toujours de 5, ce qui donne à la fois la ligne et la colonne .