=RECHERCHEX(1;(A2:A7=E2)*(B2:B7=F2);C2:C7) renvoie le prix de la ligne où le produit est E2 et la taille est F2. Chaque comparaison vérifie toutes les lignes, leur produit ne vaut 1 que là où les deux sont vraies, et RECHERCHEX cherche ce 1. Elle demande Excel 2021 ou Microsoft 365 ; la version INDEX EQUIV ci-dessous fonctionne dans toutes les versions. RECHERCHEX s'appelle XLOOKUP dans un Excel anglais, le nom que montre le tableau ; vous pouvez aussi y taper les formules en français, avec des points-virgules.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Tea | Large | $3.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=RECHERCHEX(1;(A2:A7=E2)*(B2:B7=F2);C2:C7)Tea et Large se rencontrent à la ligne 5, donc G2 renvoie $3.00. Choisissez Juice et Small : $3.00 à nouveau, depuis une autre ligne. Ajoutez un quatrième argument pour le cas où aucune ligne ne correspond aux deux : =RECHERCHEX(1;(A2:A7=E2)*(B2:B7=F2);C2:C7;"No such item").
Comment fonctionnent les conditions multipliées
A2:A7=E2 compare chaque produit à E2 et renvoie six valeurs VRAI ou FAUX. Multiplier deux listes de ce type transforme VRAI en 1 et FAUX en 0, et une ligne ne vaut 1 que si elle vaut 1 dans les deux. La colonne D montre cette liste, propagée à partir d'une seule formule.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Both match | Product | Size | |
| 2 | Coffee | Small | $2.50 | 0 | Tea | Large | |
| 3 | Coffee | Large | $3.50 | 0 | |||
| 4 | Tea | Small | $2.00 | 0 | |||
| 5 | Tea | Large | $3.00 | 1 | |||
| 6 | Juice | Small | $3.00 | 0 | |||
| 7 | Juice | Large | $4.00 | 0 |
=(A2:A7=F2)*(B2:B7=G2)Seule D5 vaut 1. Modifiez F2 ou G2 et le 1 se déplace. Chaque critère supplémentaire est un *(plage=valeur) de plus, et les conditions ne sont pas forcément des égalités : *(C2:C7<3) ajoute "prix inférieur à 3". Toutes les plages doivent couvrir les mêmes lignes (A2:A7, B2:B7, C2:C7) : si la plage de résultat n'a pas la même taille que les conditions, RECHERCHEX renvoie #VALEUR! (en anglais #VALUE!, le nom qu'affiche le tableau).
INDEX EQUIV avec plusieurs critères
Pour Excel 2019 et versions antérieures, EQUIV (MATCH en anglais) peut chercher le 1 dans la même matrice, et INDEX renvoie le prix à cette position.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Coffee | Large | $3.50 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=INDEX(C2:C7;EQUIV(1;(A2:A7=E2)*(B2:B7=F2);0))Coffee et Large est la position 2 de la matrice, et INDEX renvoie $3.50. En français, la formule s'écrit =INDEX(C2:C7;EQUIV(1;(A2:A7=E2)*(B2:B7=F2);0)). Dans Excel 2019 et versions antérieures, c'est une formule matricielle : appuyez sur Ctrl+Maj+Entrée (Cmd+Maj+Entrée sur Mac) au lieu d'Entrée, et Excel l'affiche entre accolades. Un simple Entrée y renvoie généralement #N/A ou #VALEUR!. Dans Excel 365, Entrée suffit. La forme à un seul critère se trouve sur la page INDEX et EQUIV.
Assembler les critères en une seule clé
L'autre méthode consiste à transformer deux critères en un seul en les assemblant. RECHERCHEV a besoin des valeurs assemblées dans une colonne d'aide au début de la table (la page RECHERCHEV montre cette version). RECHERCHEX peut assembler les plages dans la formule, sans colonne d'aide.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Size | Price | Product | Size | Price | |
| 2 | Coffee | Small | $2.50 | Juice | Large | $4.00 | |
| 3 | Coffee | Large | $3.50 | ||||
| 4 | Tea | Small | $2.00 | ||||
| 5 | Tea | Large | $3.00 | ||||
| 6 | Juice | Small | $3.00 | ||||
| 7 | Juice | Large | $4.00 |
=RECHERCHEX(E2&"|"&F2;A2:A7&"|"&B2:B7;C2:C7)A2:A7&"|"&B2:B7 construit six clés comme Juice|Large, et RECHERCHEX trouve Juice|Large parmi elles : $4.00. Placez un séparateur entre les parties. Sans lui, "AB" et "C" donnent le même "ABC" que "A" et "BC", et la recherche peut renvoyer la mauvaise ligne.
Si la valeur voulue est un nombre et que chaque combinaison n'apparaît qu'une fois, SOMME.SI.ENS (SUMIFS en anglais) donne la même réponse sans aucune matrice : =SOMME.SI.ENS(C2:C7;A2:A7;E2;B2:B7;F2). Elle renvoie 0 au lieu d'une erreur quand rien ne correspond, ce qui peut masquer une faute de frappe.
Renvoyer toutes les correspondances avec FILTRE
RECHERCHEX et INDEX EQUIV renvoient la première ligne qui correspond. Quand plusieurs lignes correspondent et que vous les voulez toutes, utilisez FILTRE (FILTER en anglais) avec les mêmes conditions.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Matches | ||
| 2 | North | Laptop | Q1 | 120 | Q1 | 210 | |
| 3 | North | Phone | Q1 | 210 | Q2 | 225 | |
| 4 | South | Laptop | Q1 | 95 | Q3 | 240 | |
| 5 | North | Phone | Q2 | 225 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | North | Laptop | Q2 | 135 | |||
| 8 | North | Phone | Q3 | 240 |
=FILTRE(C2:D8;(A2:A8="North")*(B2:B8="Phone"))Trois lignes sont North et Phone, donc F2 propage leurs trimestres et leurs ventes dans F2:G4. En français : =FILTRE(C2:D8;(A2:A8="North")*(B2:B8="Phone")). Remplacez A3 par South et la liste se réduit à deux lignes. Si aucune ligne ne correspond, FILTRE renvoie #CALC! ; un troisième argument comme "None" affiche un texte à la place. D'autres options se trouvent sur la page FILTRE.
Exercice : trois critères
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Product | Quarter | Sales | Region | South | |
| 2 | North | Laptop | Q1 | 120 | Product | Laptop | |
| 3 | North | Laptop | Q2 | 135 | Quarter | Q2 | |
| 4 | North | Phone | Q1 | 210 | Sales | ||
| 5 | South | Laptop | Q1 | 95 | |||
| 6 | South | Phone | Q2 | 180 | |||
| 7 | South | Laptop | Q2 | 110 | |||
| 8 | North | Phone | Q2 | 225 |
À vous : Renvoyez en G4 les ventes de la région de G1, du produit de G2 et du trimestre de G3.
Questions fréquentes
Comment utiliser RECHERCHEX avec plusieurs critères ?
Multipliez une comparaison par critère et cherchez 1 : =RECHERCHEX(1;(A2:A7=E2)*(B2:B7=F2);C2:C7). Chaque comparaison donne VRAI ou FAUX pour chaque ligne, le produit ne vaut 1 que là où toutes sont VRAI, et RECHERCHEX renvoie la première de ces lignes.
Comment faire un INDEX EQUIV avec deux critères ?
Utilisez les mêmes conditions multipliées dans EQUIV : =INDEX(C2:C7;EQUIV(1;(A2:A7=E2)*(B2:B7=F2);0)). Dans Excel 2019 et versions antérieures, validez-la avec Ctrl+Maj+Entrée (Cmd+Maj+Entrée sur Mac).
RECHERCHEV peut-elle utiliser deux critères ?
Pas directement. Ajoutez au début de la table une colonne d'aide qui assemble les deux valeurs, comme =A2&"|"&B2, puis cherchez la valeur assemblée : =RECHERCHEV(E2&"|"&F2;table_aide;col;FAUX).
SOMME.SI.ENS peut-elle remplacer une recherche à deux critères ?
Oui, quand la valeur est un nombre et que chaque combinaison n'apparaît qu'une fois : =SOMME.SI.ENS(C2:C7;A2:A7;E2;B2:B7;F2). Elle renvoie 0 au lieu de #N/A quand aucune ligne ne correspond, et additionne les valeurs si une combinaison apparaît deux fois.
Comment faire une recherche avec des critères OU ?
Additionnez les conditions au lieu de les multiplier : (A2:A7="Tea")+(A2:A7="Juice") vaut 1 ou plus là où l'une des deux est vraie. Cherchez une valeur supérieure à 0, par exemple avec =RECHERCHEX(VRAI;((A2:A7="Tea")+(A2:A7="Juice"))>0;C2:C7).
