La fonction XLOOKUP dans Excel
RECHERCHEX (Anglais: XLOOKUP) recherche une valeur dans une plage et renvoie la valeur située à la même position dans une autre plage. Contrairement à RECHERCHEV (Anglais: VLOOKUP), les deux plages peuvent se trouver n'importe où : le résultat peut provenir d'une colonne située à gauche, et vous n'avez pas à compter les numéros de colonne.
Elle est disponible dans Excel 2021, Excel 2024, Microsoft 365 et Excel pour le web. Elle n'existe pas dans Excel 2019 ni dans les versions antérieures : les fichiers partagés avec ces versions ont donc encore besoin de RECHERCHEV (Anglais: VLOOKUP) ou de INDEX/MATCH.
Syntaxe
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | Ce que cela signifie |
|---|---|
| lookup_value | La valeur à rechercher. |
| lookup_array | La colonne (ou la ligne) dans laquelle effectuer la recherche. |
| return_array | La colonne (ou la ligne) dans laquelle prendre la valeur à renvoyer. Même taille que lookup_array. |
| if_not_foundfacultatif | Ce qui s'affiche si rien ne correspond, au lieu de #N/A. |
| match_modefacultatif | 0 = correspondance exacte (par défaut), -1 = correspondance exacte ou valeur inférieure suivante, 1 = correspondance exacte ou valeur supérieure suivante, 2 = correspondance avec caractères génériques. |
| search_modefacultatif | 1 = recherche à partir du premier élément (par défaut), -1 = recherche à partir du dernier élément, 2 = recherche dichotomique (liste triée par ordre croissant), -2 = recherche dichotomique (liste triée par ordre décroissant). |
Exemples
1. Trouver un prix à partir d'un ID produit
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID produit | Produit | Prix | Stock | ID produit | P-102 | |
| 2 | P-101 | Lampe de bureau | 24.5 | 40 | Prix | 149 | |
| 3 | P-102 | Chaise de bureau | 149 | 12 | |||
| 4 | P-103 | Support d'écran | 39.9 | 25 | |||
| 5 | P-104 | Clavier | 59 | 0 |
Recherchez dans la colonne des ID A2:A5, et renvoyez le prix de la colonne C2:C5. La correspondance exacte est le comportement par défaut : il n'est donc pas nécessaire d'ajouter FALSE.
2. Chercher vers la gauche
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID produit | Produit | Prix | Stock | ID du clavier | |
| 2 | P-101 | Lampe de bureau | 24.5 | 40 | P-104 | |
| 3 | P-102 | Chaise de bureau | 149 | 12 | ||
| 4 | P-103 | Support d'écran | 39.9 | 25 | ||
| 5 | P-104 | Clavier | 59 | 0 |
La colonne de recherche (B) se trouve à droite de la colonne du résultat (A). RECHERCHEV (Anglais: VLOOKUP) ne peut pas faire cela, alors que RECHERCHEX (Anglais: XLOOKUP) le peut.
3. Votre propre message lorsque rien n'est trouvé
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID produit | Produit | Prix | Stock | Produit P-999 | |
| 2 | P-101 | Lampe de bureau | 24.5 | 40 | Produit introuvable | |
| 3 | P-102 | Chaise de bureau | 149 | 12 | ||
| 4 | P-103 | Support d'écran | 39.9 | 25 | ||
| 5 | P-104 | Clavier | 59 | 0 |
Le 4e argument remplace #N/A : vous n'avez donc pas besoin de SIERREUR (Anglais: IFERROR) autour de la formule.
4. Correspondance approchée : tranches de prix
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Poids à partir de (kg) | Frais d'expédition | Poids du colis | 6.3 | |
| 2 | 0 | 4.99 | Frais d'expédition | 12 | |
| 3 | 2 | 7.5 | |||
| 4 | 5 | 12 | |||
| 5 | 10 | 20 |
match_mode -1 signifie « correspondance exacte, ou sinon la valeur inférieure suivante ». 6.3 ne figure pas dans la liste, la formule utilise donc 5 → 12. Contrairement à RECHERCHEV (Anglais: VLOOKUP), cela fonctionne même si la liste n'est pas triée.
Erreurs courantes
Erreur #NAME?. La version d'Excel ne dispose pas de RECHERCHEX (Anglais: XLOOKUP) (Excel 2019 ou une version antérieure).
Correction : Utilisez RECHERCHEV (Anglais: VLOOKUP) ou INDEX/MATCH pour les fichiers qui doivent pouvoir s'ouvrir dans les anciennes versions.
Erreur #VALUE!. Les plages de recherche et de retour n'ont pas la même taille, par exemple A2:A5 et C2:C6.
Correction : Faites en sorte que les deux plages commencent et se terminent sur les mêmes lignes.
Les plages se décalent lorsque vous recopiez la formule vers le bas.
Correction : Verrouillez-les avec le signe $ :
=RECHERCHEX(G1;$A$2:$A$5;$C$2:$C$5).
Questions fréquemment posées
Quelle est la différence entre XLOOKUP et VLOOKUP ?
RECHERCHEX (Anglais: XLOOKUP) utilise des plages de recherche et de résultat distinctes (il peut donc chercher vers la gauche), privilégie une correspondance exacte par défaut et dispose d'une valeur « introuvable » intégrée. RECHERCHEV (Anglais: VLOOKUP) fonctionne dans toutes les versions d'Excel.
XLOOKUP peut-il renvoyer plusieurs colonnes à la fois ?
Oui. Si return_array comporte plusieurs colonnes, par exemple B2:D5, le résultat se déverse dans les cellules voisines dans Excel 2021 et les versions plus récentes.
Comment trouver la dernière correspondance au lieu de la première ?
Réglez search_mode sur -1 : =RECHERCHEX(G1;A2:A5;C2:C5;"";0;-1).
Fonctions associées
- VLOOKUPVLOOKUP recherche une valeur dans la première colonne d'un tableau et renvoie une valeur de la même ligne, dans une autre colonne.
- INDEXINDEX renvoie la valeur située à une ligne et une colonne données d'une plage.
- MATCHMATCH renvoie la position d'une valeur dans une liste, par exemple que « Lena » est le 3e nom.
- IFERRORIFERROR renvoie la valeur de votre choix lorsqu'une formule produit une erreur comme #N/A ou #DIV/0!, et le résultat normal sinon.