Generisk formel
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Resumé
For at slå den nyeste filversion på en liste op kan du bruge en formel baseret på LOOKUP-funktionen sammen med ISNUMBER- og FIND-funktionerne. I det viste eksempel er formlen i celle G7:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
hvor "filer" er det navngivne område B5: B11.
Sammenhæng
I dette eksempel har vi et antal filversioner opført i en tabel med en dato og et brugernavn. Bemærk, at filnavne gentages med en tæller i slutningen som et revisionsnummer - 001, 002, 003 osv.
Med et filnavn ønsker vi at hente navnet på den sidste eller seneste revision. Der er to udfordringer:
- Udfordringen er, at versionskoderne i slutningen af filnavnene gør det sværere at matche filnavnet.
- Som standard returnerer Excel-matchformler det første match, ikke det sidste match.
For at overvinde disse udfordringer er vi nødt til at bruge nogle vanskelige teknikker.
Forklaring
Denne formel bruger LOOKUP-funktionen til at finde og hente det sidst matchende filnavn. Opslagsværdien er 2, og opslagsvektoren oprettes med dette:
1/(ISNUMBER(FIND(G6,files)))
Inde i dette uddrag søger FIND-funktionen efter værdien i G6 inden for de navngivne rækkevidde "filer" (B5: B11). Resultatet er en matrix som denne:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Her repræsenterer tallet 1 et match, og fejlen #VALUE repræsenterer et filnavn, der ikke matcher. Denne matrix går ind i ISNUMBER-funktionen og kommer sådan ud:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Fejlværdier er nu FALSE, og tallet 1 er nu SAND. Dette overvinder udfordring nr. 1, vi har nu en matrix, der tydeligt viser, hvilke filer på listen der indeholder det filnavn, der er af interesse.
Dernæst bruges arrayet som nævneren med 1 som tæller. Resultatet ser sådan ud:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
som går ind i LOOKUP som lookup_vector. Dette er en vanskelig løsning til at udfordre nr. 2. LOOKUP-funktionen fungerer kun i omtrentlig matchningstilstand og ignorerer automatisk fejlværdier. Dette betyder, at med 2 som en opslagsværdi, vil VLOOKUP forsøge at finde 2, mislykkes og gå tilbage til det forrige nummer (i dette tilfælde matchende den sidste 1 i position 7). Endelig bruger LOOKUP 7 som et indeks til at hente den 7. fil på listen over filer.
Håndtering af blanke opslag
Mærkeligt nok returnerer FIND-funktionen 1, hvis opslagsværdien er en tom streng (""). For at beskytte mod en falsk match kan du pakke formlen i IF og teste for en tom opslag:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
