Generisk formel
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Sammanfattning
För att leta upp den senaste filversionen i en lista kan du använda en formel baserad på LOOKUP-funktionen tillsammans med ISNUMBER- och FIND-funktionerna. I exemplet som visas är formeln i cell G7:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
där "filer" är det namngivna området B5: B11.
Sammanhang
I det här exemplet har vi ett antal filversioner listade i en tabell med datum och användarnamn. Observera att filnamn upprepas med en räknare i slutet som ett revisionsnummer - 001, 002, 003, etc.
Med ett filnamn vill vi hämta namnet på den senaste eller senaste versionen. Det finns två utmaningar:
- Utmaningen är att versionskoderna i slutet av filnamnen gör det svårare att matcha filnamnet.
- Som standard returnerar Excel-matchningsformler den första matchningen, inte den senaste matchningen.
För att övervinna dessa utmaningar måste vi använda några knepiga tekniker.
Förklaring
Denna formel använder LOOKUP-funktionen för att hitta och hämta det senast matchande filnamnet. Uppslagsvärdet är 2 och uppslagningsvektorn skapas med detta:
1/(ISNUMBER(FIND(G6,files)))
Inuti detta kodavsnitt letar FIND-funktionen efter värdet i G6 i de namngivna intervallfilerna (B5: B11). Resultatet är en array som denna:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
Här representerar siffran 1 en matchning och #VALUE-felet representerar ett filnamn som inte matchar. Denna matris går in i ISNUMBER-funktionen och kommer ut så här:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Felvärdena är nu FALSE och siffran 1 är nu TRUE. Detta övervinner utmaning nr 1, vi har nu en matris som tydligt visar vilka filer i listan som innehåller filnamnet av intresse.
Därefter används arrayen som nämnaren med 1 som täljare. Resultatet ser ut så här:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
som går in på LOOKUP som lookup_vector. Detta är en knepig lösning för att utmana nr 2. LOOKUP-funktionen fungerar endast i ungefärligt matchningsläge och ignorerar automatiskt felvärden. Detta innebär att med 2 som ett uppslagsvärde kommer VLOOKUP att försöka hitta 2, misslyckas och gå tillbaka till föregående nummer (i det här fallet matchar den sista 1 i position 7). Slutligen använder LOOKUP 7 som ett index för att hämta den 7: e filen i listan över filer.
Hantera tomma sökningar
Konstigt nog returnerar HITTA-funktionen 1 om uppslagsvärdet är en tom sträng (""). För att skydda dig mot en falsk matchning kan du slå in formeln i IF och testa för en tom sökning:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
