Generieke formule
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Samenvatting
Met grote sets gegevens kan VERT.ZOEKEN met exacte overeenkomsten pijnlijk traag zijn, maar u kunt VERT.ZOEKEN bliksemsnel maken door twee VERT.ZOEKEN te gebruiken, zoals hieronder wordt uitgelegd.
Opmerkingen:
- Als u een kleinere set gegevens heeft, is deze aanpak overdreven. Gebruik het alleen met grote gegevenssets als snelheid er echt toe doet.
- U moet de gegevens sorteren op zoekwaarde om deze truc te laten werken.
- In dit voorbeeld worden benoemde bereiken gebruikt. Als u geen benoemde bereiken wilt gebruiken, gebruikt u in plaats daarvan absolute verwijzingen.
Exact-match VERT.ZOEKEN is traag
Wanneer u VERT.ZOEKEN gebruikt in "exacte overeenkomstmodus" voor een grote set gegevens, kan dit de rekentijd in een werkblad aanzienlijk vertragen. Met bijvoorbeeld 50.000 records of 100.000 records kan de berekening minuten duren.
Exacte overeenkomst wordt ingesteld door FALSE of nul op te geven als het vierde argument:
=VLOOKUP(val,data,col,FALSE)
De reden dat VERT.ZOEKEN in deze modus traag is, is omdat het elk afzonderlijk record in de gegevensset moet controleren totdat er een overeenkomst is gevonden. Dit wordt ook wel een lineaire zoekopdracht genoemd.
VERT.ZOEKEN bij benadering is erg snel
In de geschatte overeenkomstmodus is VERT.ZOEKEN extreem snel. Om VERT.ZOEKEN bij benadering te gebruiken, moet u uw gegevens sorteren op de eerste kolom (de opzoekkolom) en vervolgens TRUE specificeren voor het 4e argument:
=VLOOKUP(val,data,col,TRUE)
(VERT.ZOEKEN is standaard waar, wat een enge standaard is, maar dat is een ander verhaal).
Bij zeer grote gegevenssets kan het wijzigen naar VERT.ZOEKEN bij benadering een dramatische snelheidsverhoging betekenen.
Dus, no-brainer, toch? Sorteer gewoon de gegevens, gebruik een geschatte overeenkomst en u bent klaar.
Niet zo snel (heh).
Het probleem met VERT.ZOEKEN in "geschatte overeenkomst" -modus is dit: VERT.ZOEKEN zal geen fout weergeven als de opzoekwaarde niet bestaat. Erger nog, het resultaat kan er volkomen normaal uitzien, ook al is het totaal verkeerd (zie voorbeelden). Niet iets dat je aan je baas wilt uitleggen.
De oplossing is om VERT.ZOEKEN twee keer te gebruiken, beide keren in de geschatte overeenkomstmodus:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Uitleg
De eerste instantie van VERT.ZOEKEN zoekt eenvoudig de opzoekwaarde op (de id in dit voorbeeld):
=IF(VLOOKUP(id,data,1,TRUE)=id
en geeft alleen WAAR terug als de opzoekwaarde is gevonden. In dat geval
voert de formule VERT.ZOEKEN opnieuw uit in geschatte overeenkomstmodus om een waarde uit die tabel op te halen:
VLOOKUP(id,data,col,TRUE)
Er is geen gevaar voor een ontbrekende opzoekwaarde, aangezien het eerste deel van de formule al heeft gecontroleerd of het er is.
Als de opzoekwaarde niet wordt gevonden, wordt het gedeelte "waarde als ONWAAR" van de functie ALS uitgevoerd en kunt u elke gewenste waarde retourneren. In dit voorbeeld gebruiken we NA () we retourneren een # N / A-fout, maar je zou ook een bericht als "Ontbrekend" of "Niet gevonden" kunnen retourneren.
Onthoud: je moet de gegevens sorteren op zoekwaarde om deze truc te laten werken.
