Chapitre 17 · 17.4
Rechercher selon deux critères à la fois
RECHERCHEV et XLOOKUP recherchent une seule valeur (voir RECHERCHEV renvoie #N/A : comment résoudre le problème et XLOOKUP : recherche vers la gauche et avec un message personnalisé). Dans la pratique, vous cherchez souvent selon deux critères à la fois : le prix d'un bouquet en taille L, le tarif d'un client pour une année donnée, le stock d'un produit dans une couleur précise. Il existe trois bonnes méthodes pour cela, qui fonctionnent toutes avec une simple touche Entrée. Une quatrième méthode, très répandue sur Internet, ne fonctionne pas telle quelle dans Euro-Office.
Prenons comme exemple la liste de prix de Bloemisterij Het Zonnetje. Le même produit y figure plusieurs fois, dans différentes tailles :
| A | B | C | |
|---|---|---|---|
| 1 | Produit | Taille | Prix |
| 2 | Bouquet | S | 17,50 |
| 3 | Bouquet | M | 24,50 |
| 4 | Bouquet | L | 34,50 |
| 5 | Composition funéraire | M | 65,00 |
| 6 | Composition funéraire | L | 89,00 |
| 7 | Bouquet de mariée | M | 79,50 |
Le produit recherché se trouve en F2 (Bouquet), la taille en G2 (L). Nous cherchons 34,50.
Méthode 1 : XLOOKUP avec des valeurs concaténées
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"introuvable")
Le signe & colle du texte bout à bout. F2&"|"&G2 devient "Bouquet|L". A2:A7&"|"&B2:B7 crée la même combinaison pour chaque ligne de la liste ("Bouquet|S", "Bouquet|M" et ainsi de suite), et XLOOKUP y effectue la recherche. La barre | placée entre les deux évite que deux combinaisons différentes donnent par hasard le même texte. Si la combinaison n'existe pas, par exemple une composition funéraire en taille S, vous obtenez "introuvable".

Si vous regardez la barre de formule après la saisie, vous verrez un @ devant A2:A7 et B2:B7. Euro-Office l'ajoute de lui-même ; le résultat reste correct.
Méthode 2 : FILTRE pour tous les résultats
=FILTRE(C2:C7;(A2:A7=F2)*(B2:B7=G2);"aucun")
FILTRE ne renvoie pas seulement la première ligne, mais toutes les lignes qui remplissent les deux critères. Si une combinaison apparaît deux fois, vous voyez les deux prix l'un sous l'autre. Veillez donc à laisser de la place sous la formule (voir Des listes qui se mettent à jour toutes seules avec FILTRE, UNIQUE et TRIER). Pour un « ou » au lieu d'un « et », utilisez le signe plus : (A2:A7="Bouquet")+(A2:A7="Composition funéraire").
Méthode 3 : SOMME.SI.ENS si la combinaison est unique
=SOMME.SI.ENS(C2:C7;A2:A7;F2;B2:B7;G2)
Cela peut paraître étrange, puisque vous ne voulez rien additionner. Mais si chaque combinaison n'apparaît qu'une seule fois, la somme correspond simplement à ce prix unique. L'avantage : SOMME.SI.ENS est simple, rapide et reconnue par toutes les versions d'Excel. L'inconvénient : si la combinaison n'existe pas, vous obtenez 0 (0,00 €) au lieu d'un message. Et si la combinaison figure par erreur deux fois dans la liste, les deux prix sont additionnés.
Ce que vous trouvez sur Internet : INDEX et EQUIV avec une multiplication
De nombreux sites consacrés à Excel proposent =INDEX(C2:C7;EQUIV(1;(A2:A7=F2)*(B2:B7=G2);0)). Dans Euro-Office, cette formule validée avec une simple touche Entrée renvoie #N/A, même si la combinaison existe. Si vous la validez avec Ctrl+Shift+Entrée, la formule reçoit des accolades {…} et fonctionne. C'est facile à oublier, utilisez donc plutôt XLOOKUP ou FILTRE ci-dessus.
Rechercher par ligne et par colonne
Si votre liste de prix se présente sous forme de tableau, avec les produits les uns sous les autres et les tailles côte à côte (une matrice), utilisez INDEX avec deux fois EQUIV. Avec les produits en A11:A13, les tailles S, M et L en B10:D10 et les prix en B11:D13 :
=INDEX(B11:D13;EQUIV(F2;A11:A13;0);EQUIV(G2;B10:D10;0))
Le premier EQUIV recherche la ligne du produit, le second la colonne de la taille. INDEX récupère la cellule située à leur intersection.
Quelle méthode choisir ?
| Situation | Meilleur choix |
|---|---|
| Une seule réponse, avec un message clair si elle n'existe pas | XLOOKUP avec des valeurs concaténées |
| Le fichier doit aussi fonctionner dans une ancienne version d'Excel | SOMME.SI.ENS |
| Il peut y avoir plusieurs résultats | FILTRE |
| Tableau de prix avec les tailles en en-têtes de colonnes | INDEX avec deux fois EQUIV |
Pièges à éviter
- #N/A alors que la combinaison figure bien dans la liste : souvent un espace après une valeur ("Bouquet ") ou un nombre enregistré sous forme de texte. Voir RECHERCHEV renvoie #N/A : comment résoudre le problème.
- Plages de longueurs différentes : =XLOOKUP(F2;A2:A7;C2:C50) renvoie #VALEUR!. Faites commencer et finir toutes les plages sur la même ligne.
- Des colonnes entières (A:A) dans ces formules ralentissent un fichier volumineux, car Euro-Office concatène alors un million de lignes. Utilisez une plage fixe comme A2:A500. Voir Accélérer un tableur lent.
- Un collègue qui utilise Excel 2019 ou une version plus ancienne verra #NOM? avec XLOOKUP et FILTRE.
