Formule générique
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Sommaire
Avec de grands ensembles de données, la correspondance exacte VLOOKUP peut être extrêmement lente, mais vous pouvez accélérer la recherche VLOOKUP en utilisant deux VLOOKUPS, comme expliqué ci-dessous.
Remarques:
- Si vous disposez d'un ensemble de données plus petit, cette approche est excessive. Utilisez-le uniquement avec de grands ensembles de données lorsque la vitesse compte vraiment.
- Vous devez trier les données par valeur de recherche pour que cette astuce fonctionne.
- Cet exemple utilise des plages nommées. Si vous ne souhaitez pas utiliser de plages nommées, utilisez plutôt des références absolues.
VLOOKUP de correspondance exacte est lent
Lorsque vous utilisez RECHERCHEV en "mode de correspondance exacte" sur un grand ensemble de données, cela peut vraiment ralentir le temps de calcul dans une feuille de calcul. Avec, par exemple, 50 000 enregistrements ou 100 000 enregistrements, le calcul peut prendre quelques minutes.
La correspondance exacte est définie en fournissant FALSE ou zéro comme quatrième argument:
=VLOOKUP(val,data,col,FALSE)
La raison pour laquelle RECHERCHEV dans ce mode est lente est qu'il doit vérifier chaque enregistrement de l'ensemble de données jusqu'à ce qu'une correspondance soit trouvée. Ceci est parfois appelé une recherche linéaire.
VLOOKUP de correspondance approximative est très rapide
En mode de correspondance approximative, VLOOKUP est extrêmement rapide. Pour utiliser VLOOKUP de correspondance approximative, vous devez trier vos données par la première colonne (la colonne de recherche), puis spécifier TRUE pour le 4ème argument:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP par défaut sur true, ce qui est une valeur par défaut effrayante, mais c'est une autre histoire).
Avec de très grands ensembles de données, le passage à VLOOKUP de correspondance approximative peut entraîner une augmentation spectaculaire de la vitesse.
Alors, pas de problème, non? Triez simplement les données, utilisez une correspondance approximative et vous avez terminé.
Pas si vite (heh).
Le problème avec RECHERCHEV en mode "correspondance approximative" est le suivant: RECHERCHEV n'affichera pas d'erreur si la valeur de recherche n'existe pas. Pire encore, le résultat peut sembler tout à fait normal, même s'il est totalement faux (voir exemples). Ce n'est pas quelque chose que vous voulez expliquer à votre patron.
La solution consiste à utiliser RECHERCHEV deux fois, les deux fois en mode de correspondance approximative:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Explication
La première instance de RECHERCHEV recherche simplement la valeur de recherche (l' ID dans cet exemple):
=IF(VLOOKUP(id,data,1,TRUE)=id
et renvoie VRAI uniquement lorsque la valeur de recherche est trouvée. Dans ce cas,
la formule exécute à nouveau RECHERCHEV en mode de correspondance approximative pour récupérer une valeur de cette table:
VLOOKUP(id,data,col,TRUE)
Il n'y a pas de danger d'une valeur de recherche manquante, puisque la première partie de la formule a déjà été vérifiée pour s'assurer qu'elle est là.
Si la valeur de recherche n'est pas trouvée, la partie «valeur si FALSE» de la fonction IF s'exécute et vous pouvez renvoyer la valeur de votre choix. Dans cet exemple, nous utilisons NA (), nous retournons une erreur # N / A, mais vous pouvez également renvoyer un message comme "Missing" ou "Not found".
N'oubliez pas: vous devez trier les données par valeur de recherche pour que cette astuce fonctionne.
