Generisk formel
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Sammendrag
Med store datasett kan nøyaktig samsvar VLOOKUP være smertefullt sakte, men du kan få VLOOKUP til å lyse raskt ved å bruke to VLOOKUPS, som forklart nedenfor.
Merknader:
- Hvis du har et mindre datasett, er denne tilnærmingen overkill. Bruk den bare med store datasett når hastigheten virkelig teller.
- Du må sortere dataene etter oppslagsverdi for at dette trikset skal fungere.
- Dette eksemplet bruker navngitte områder. Hvis du ikke vil bruke navngitte områder, bruk absolutt referanser i stedet.
Nøyaktig match VLOOKUP går tregt
Når du bruker VLOOKUP i "eksakt samsvarsmodus" på et stort datasett, kan det virkelig redusere beregningstiden i et regneark. Med for eksempel 50.000 poster eller 100.000 poster, kan beregning ta minutter.
Nøyaktig samsvar settes ved å oppgi FALSE eller null som det fjerde argumentet:
=VLOOKUP(val,data,col,FALSE)
Årsaken til at LOOKUP i denne modusen er treg, er fordi den må sjekke hver eneste post i datasettet til en kamp er funnet. Dette blir noen ganger referert til som et lineært søk.
Omtrentlig kamp VLOOKUP er veldig rask
I omtrentlig kampmodus er VLOOKUP ekstremt rask. For å bruke VLOOKUP med omtrentlig samsvar, må du sortere dataene dine etter den første kolonnen (oppslagskolonnen), og deretter angi SANT for det fjerde argumentet:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP er som standard sant, noe som er en skummel standard, men det er en annen historie).
Med veldig store datasett kan det bety en dramatisk hastighetsøkning å bytte til VLOOKUP med omtrentlig kamp.
Så ikke brainer, ikke sant? Bare sorter dataene, bruk omtrentlig samsvar, så er du ferdig.
Ikke så fort (heh).
Problemet med VLOOKUP i "approximate match" -modus er dette: VLOOKUP vil ikke vise en feil hvis oppslagsverdien ikke eksisterer. Verre, resultatet kan se helt normalt ut, selv om det er helt feil (se eksempler). Ikke noe du vil forklare sjefen din.
Løsningen er å bruke VLOOKUP to ganger, begge ganger i omtrentlig kampmodus:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Forklaring
Den første forekomsten av VLOOKUP slår ganske enkelt opp oppslagsverdien ( id i dette eksemplet):
=IF(VLOOKUP(id,data,1,TRUE)=id
og returnerer SANT bare når oppslagsverdien er funnet. I så fall
kjører formelen VLOOKUP igjen i omtrentlig kampmodus for å hente en verdi fra den tabellen:
VLOOKUP(id,data,col,TRUE)
Det er ingen fare for manglende oppslagsverdi, siden den første delen av formelen allerede er sjekket for å sikre at den er der.
Hvis oppslagsverdien ikke blir funnet, kjøres "verdien hvis FALSK" -delen av IF-funksjonen, og du kan returnere hvilken som helst verdi du vil. I dette eksemplet bruker vi NA () vi returnerer en # N / A-feil, men du kan også returnere en melding som "Mangler" eller "Ikke funnet".
Husk: du må sortere dataene etter oppslagsverdi for at dette trikset skal fungere.
