Formule générique
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Sommaire
Pour rechercher la dernière version de fichier dans une liste, vous pouvez utiliser une formule basée sur la fonction LOOKUP avec les fonctions ISNUMBER et FIND. Dans l'exemple illustré, la formule de la cellule G7 est:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
où "fichiers" est la plage nommée B5: B11.
Le contexte
Dans cet exemple, nous avons un certain nombre de versions de fichiers répertoriées dans un tableau avec une date et un nom d'utilisateur. Notez que les noms de fichiers sont répétés avec un compteur à la fin comme numéro de révision - 001, 002, 003, etc.
Étant donné un nom de fichier, nous voulons récupérer le nom de la dernière ou dernière révision. Il y a deux défis:
- Le défi est que les codes de version à la fin des noms de fichiers rendent plus difficile la correspondance avec le nom de fichier.
- Par défaut, les formules de correspondance Excel renverront la première correspondance, pas la dernière.
Pour surmonter ces défis, nous devons utiliser des techniques délicates.
Explication
Cette formule utilise la fonction LOOKUP pour rechercher et récupérer le dernier nom de fichier correspondant. La valeur de recherche est 2 et le lookup_vector est créé avec ceci:
1/(ISNUMBER(FIND(G6,files)))
À l'intérieur de cet extrait de code, la fonction FIND recherche la valeur dans G6 dans la plage nommée "fichiers" (B5: B11). Le résultat est un tableau comme celui-ci:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Ici, le numéro 1 représente une correspondance et l'erreur #VALUE représente un nom de fichier non correspondant. Ce tableau entre dans la fonction ISNUMBER et se présente comme ceci:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Les valeurs d'erreur sont maintenant FALSE et le nombre 1 est maintenant TRUE. Cela surmonte le défi n ° 1, nous avons maintenant un tableau qui montre clairement quels fichiers de la liste contiennent le nom de fichier d'intérêt.
Ensuite, le tableau est utilisé comme dénominateur avec 1 comme numérateur. Le résultat ressemble à ceci:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
qui entre dans LOOKUP en tant que lookup_vector. C'est une solution délicate au défi n ° 2. La fonction LOOKUP fonctionne uniquement en mode de correspondance approximative et ignore automatiquement les valeurs d'erreur. Cela signifie qu'avec 2 comme valeur de recherche, VLOOKUP essaiera de trouver 2, échouera et reviendra au numéro précédent (dans ce cas correspondant au dernier 1 en position 7). Enfin, LOOKUP utilise 7 comme un index pour récupérer le 7ème fichier dans la liste des fichiers.
Gestion des recherches vides
Curieusement, la fonction FIND renvoie 1 si la valeur de recherche est une chaîne vide (""). Pour vous protéger contre une fausse correspondance, vous pouvez envelopper la formule dans IF et tester une recherche vide:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
