Comment trouver une valeur en fonction de deux critères ou plus dans Excel ?

Imaginez que vous travaillez avec un tableau de ventes de fruits comme celui présenté ci-dessous. Vous pourriez avoir besoin de retrouver le montant des ventes en fonction de plusieurs critères : le type de fruit, la date de vente et le poids. Grâce aux formules matricielles dans Excel, vous pouvez récupérer ces valeurs de manière efficace, même lorsqu’elles doivent satisfaire plusieurs conditions simultanément. Cette approche est à la fois souple et parfaitement adaptée aux jeux de données où il s’agit d’identifier une valeur unique correspondant à plusieurs critères précis.

Formule matricielle 1 : rechercher une valeur selon deux critères ou davantage dans Excel

La structure générale de cette formule matricielle est la suivante :

{=INDEX(tableau;EQUIV(1;(critère1=plage_recherche1)*(critère2=plage_recherche2)…*(critère_n=plage_recherche_n);0))}

Par exemple, si vous souhaitez trouver le montant des ventes de manguevendue le 9/3/2019, saisissez la formule suivante dans une cellule vide et appuyez sur Ctrl+Maj+Entréepour la valider en tant que formule matricielle :

=INDEX(F3:F22;EQUIV(1;(J3=B3:B22)*(J4=C3:C22);0))

Remarque: dans cet exemple,

• F3:F22 est la colonne « Montant » à partir de laquelle vous souhaitez récupérer la valeur.
• B3:B22 correspond à la colonne « Date » ; C3:C22 correspond à la colonne « Fruit ».
• J3 est la date choisie comme premier critère ; J4 est le nom du fruit utilisé comme deuxième critère.

Ajouter davantage de critères est un jeu d’enfant. Par exemple, pour rechercher le montant des ventes de mangue le 9/3/2019 avec un poids de 211, il vous suffit d’ajouter une troisième condition à la fois dans la fonction EQUIV et dans les plages de recherche, comme suit :

=INDEX(F3:F22;EQUIV(1;(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22);0))

Après avoir saisi la formule, appuyez à nouveau sur Ctrl + Maj + Entrée pour la valider. Le résultat affichera le montant des ventes répondant à tous les critères spécifiés.

Formule matricielle 2 : rechercher une valeur selon deux critères ou davantage dans Excel par concaténation

Vous pouvez également recourir à la concaténation dans votre formule pour adopter une approche différente, particulièrement si vous recherchez une structure plus compacte. La formule de base est la suivante :

=INDEX(tableau;EQUIV(critère1&critère2…&critèreN;plage_recherche1&plage_recherche2…&plage_rechercheN;0);0)

Par exemple, pour récupérer le montant des ventes d’un fruit ayant un poids de 242le 9/1/2019:

=INDEX(F3:F22;EQUIV(J3&J4B3:B22&C3:C22;0);0)

Remarque: ici,

• F3:F22 correspond à la colonne Montant ; B3:B22 correspond à la Date ; E3:E22 correspond à la colonne Poids.
• J3 est la date ; J5 est la valeur de poids correspondant à vos critères.

Pour plus de deux critères, élargissez à la fois les critères et les plages de recherche dans le même ordre :

=INDEX(F3:F22;EQUIV(J3&J4&J5B3:B22&C3:C22&E3:E22;0);0)

Comme précédemment, appuyez sur Ctrl + Maj + Entrée pour obtenir le résultat correct.

Ces deux méthodes de formule matricielle vous permettent de trouver la première valeur répondant à tous vos critères. Toutefois, elles exigent que les plages de cellules soient de même taille et ne renvoient qu’une seule valeur correspondante — la première trouvée. Si aucune correspondance n’est détectée, la formule affiche une erreur #N/A. Pour obtenir toutes les valeurs correspondantes, optez plutôt pour la fonction FILTRE (voir ci-dessous pour plus de détails).

Quelques conseils pratiques et remarques :

• Si vous utilisez des versions récentes d’Excel (Microsoft 365, Excel 2021), vous pouvez simplifier ce processus grâce aux formules de tableau dynamique et à la fonction FILTRE.
• Pour éviter les erreurs #N/A lorsqu’aucune correspondance n’est trouvée, encapsulez votre formule dans SIERREUR, par exemple : =SIERREUR(INDEX(F3:F22;EQUIV(1;(J3=B3:B22)*(J4=C3:C22);0));«Not found»).
• Vérifiez attentivement que vos cellules de critères ne contiennent ni espaces superflus ni données de types différents.
• Si vous obtenez une erreur après avoir appuyé uniquement sur Entrée, assurez-vous d’utiliser Ctrl + Maj + Entrée pour valider la formule en tant que formule matricielle (dans Excel 2019 et les versions antérieures).

Outre les formules, Excel propose la fonctionnalité Filtre avancé, qui vous permet de filtrer et d’extraire toutes les lignes répondant à deux critères ou plus, en affichant les résultats dans un autre emplacement. Cette approche est particulièrement utile lorsque vous souhaitez visualiser l’ensemble des enregistrements correspondant à vos conditions spécifiées, plutôt que de récupérer une seule valeur. Voici comment l’utiliser :

1. Accédez à l’onglet Données et choisissez Avancé dans le groupe Trier et filtrer pour ouvrir la boîte de dialogue Filtre avancé.

2. Dans la boîte de dialogue Filtre avancé, effectuez les réglages suivants :
(1) Sélectionnez Copier vers un autre emplacement dans la section Action.
(2) Pour Plage de liste, sélectionnez la plage contenant les données que vous souhaitez filtrer ()A1:E21 dans cet exemple).
(3) Pour Plage de critères, sélectionnez la plage contenant vos conditions de filtre ()H1:J2 ici). Vérifiez que les en-têtes de cette plage de critères correspondent exactement à ceux de votre tableau de données.
(4) Dans Copier vers, sélectionnez la première cellule où vous souhaitez coller les résultats filtrés ()H9 dans ce cas).

3. Cliquez sur OK pour appliquer le filtre.

Les lignes qui remplissent toutes les conditions de votre plage de critères seront copiées dans la zone de destination que vous avez spécifiée. Cette fonctionnalité est particulièrement utile pour examiner ou générer un rapport sur tous les enregistrements correspondant à plusieurs Conditions de filtrage simultanément.

Quelques conseils et précautions :

• Veillez à ce que les en-têtes de votre plage de critères soient strictement identiques à ceux de votre tableau principal, sous peine de compromettre le bon fonctionnement du filtre.
• Le filtre avancé prend en charge les conditions ET et OU : placer des critères sur la même ligne applique la logique ET (tous doivent être vrais), tandis que l’utilisation de lignes distinctes applique la logique OU (au moins l’un d’eux doit être vrai).
• Le filtre avancé ne se met pas à jour automatiquement lorsque vos données changent ; vous devez le réappliquer après avoir mis à jour vos données ou vos critères.
• Sachez que les cellules vides dans la plage de critères sont interprétées comme « correspondre à n’importe quelle valeur » pour ce champ.

Comparé aux solutions basées sur des formules, le filtre avancé est particulièrement adapté pour extraire des jeux de données complets répondant à vos critères, plutôt qu’une seule cellule. Toutefois, il ne convient pas aux recherches en temps réel ou fréquemment mises à jour, puisqu’il faut réexécuter le filtre après chaque modification des données.

Si vous utilisez une version récente d’Excel (Microsoft 365 ou Excel 2021 et versions ultérieures), la fonction FILTER offre une méthode dynamique et intuitive pour extraire toutes les valeurs répondant à plusieurs critères. Cette solution est fortement recommandée si vous souhaitez que vos résultats soient automatiquement mis à jour dès que les données ou les critères changent, sans avoir à saisir de formules matricielles complexes.

1. Dans une cellule vide, saisissez une formule semblable à celle-ci :

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

Dans cette formule :

• F3:F22 est votre colonne Montant.
• B3:B22 est la colonne Date, mise en correspondance avec la date située en J3.
• C3:C22 est la colonne « Fruit », mise en correspondance avec le fruit situé en J4.

Si vous souhaitez ajouter une troisième condition, par exemple faire correspondre la colonne Poids (E3:E22)à une valeur située en J5, élargissez la formule comme suit :

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

Après avoir appuyé sur Entrée, Excel affiche tous les montants qui satisfont l’ensemble des critères. Si aucune correspondance n’est trouvée, la formule renvoie une erreur #CALC!, que vous pouvez gérer à l’aide de SIERREUR :

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Avantages :

• Les résultats se mettent à jour automatiquement dès que vos données ou vos critères sont modifiés.
• Les formules sont plus simples à maintenir et à étendre que les anciennes formules matricielles.
• Renvoie toutes les correspondances, et non seulement la première valeur trouvée.

Limitation : Disponible uniquement avec Microsoft 365, Excel 2021 ou une version ultérieure. Non pris en charge par les versions antérieures.