Generisk formel
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Resumé
Med store datasæt kan nøjagtigt match VLOOKUP være smertefuldt langsomt, men du kan få VLOOKUP til at lyse hurtigt ved at bruge to VLOOKUPS, som forklaret nedenfor.
Bemærkninger:
- Hvis du har et mindre datasæt, er denne fremgangsmåde overkill. Brug det kun med store datasæt, når hastigheden virkelig tæller.
- Du skal sortere dataene efter opslagsværdi, for at dette trick kan fungere.
- Dette eksempel bruger navngivne områder. Hvis du ikke vil bruge navngivne områder, skal du bruge absolutte referencer i stedet.
VLOOKUP med nøjagtigt match er langsom
Når du bruger VLOOKUP i "nøjagtig matchningstilstand" på et stort datasæt, kan det virkelig bremse beregningstiden i et regneark. Med for eksempel 50.000 poster eller 100.000 poster kan beregningen tage minutter.
Præcis match indstilles ved at angive FALSE eller nul som det fjerde argument:
=VLOOKUP(val,data,col,FALSE)
Årsagen til, at VLOOKUP i denne tilstand er langsom, er fordi den skal kontrollere hver enkelt post i datasættet, indtil der findes et match. Dette kaldes undertiden en lineær søgning.
Omtrentlig match VLOOKUP er meget hurtig
I omtrentlig matchtilstand er VLOOKUP ekstremt hurtig. For at bruge VLOOKUP med omtrentligt match skal du sortere dine data efter den første kolonne (opslagskolonnen) og derefter angive SAND for det 4. argument:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP er som standard sand, hvilket er en skræmmende standard, men det er en anden historie).
Med meget store datasæt kan skift til VLOOKUP med omtrentlig match betyde en dramatisk hastighedsforøgelse.
Så, ikke-brainer, ikke? Bare sorter dataene, brug et omtrentligt match, så er du færdig.
Ikke så hurtigt (heh).
Problemet med VLOOKUP i "approximate match" -tilstand er dette: VLOOKUP viser ikke en fejl, hvis opslagsværdien ikke findes. Værre, resultatet kan se helt normalt ud, selvom det er helt forkert (se eksempler). Ikke noget, du vil forklare din chef.
Løsningen er at bruge VLOOKUP to gange, begge gange i omtrentlig kamptilstand:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Forklaring
Den første forekomst af VLOOKUP ser simpelthen op på opslagsværdien ( id i dette eksempel):
=IF(VLOOKUP(id,data,1,TRUE)=id
og returnerer kun SAND, når opslagsværdien er fundet. I så fald
kører formlen VLOOKUP igen i omtrentlig matchtilstand for at hente en værdi fra denne tabel:
VLOOKUP(id,data,col,TRUE)
Der er ingen fare for en manglende opslagsværdi, da den første del af formlen allerede er kontrolleret for at sikre, at den er der.
Hvis opslagsværdien ikke findes, kører "værdien hvis FALSK" -delen af IF-funktionen, og du kan returnere enhver værdi, du kan lide. I dette eksempel bruger vi NA (), vi returnerer en # N / A-fejl, men du kan også returnere en besked som "Mangler" eller "Ikke fundet".
Husk: Du skal sortere dataene efter opslagsværdi, for at dette trick kan fungere.
