Generisk formel
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Sammanfattning
Med stora uppsättningar data kan exakt matchning VLOOKUP vara smärtsamt långsam, men du kan få VLOOKUP att bli snabbare med två VLOOKUPS, som förklaras nedan.
Anmärkningar:
- Om du har en mindre uppsättning data är denna metod överdriven. Använd den bara med stora datamängder när hastigheten verkligen räknas.
- Du måste sortera data efter uppslagsvärde för att detta trick ska fungera.
- I det här exemplet används namngivna intervall. Om du inte vill använda namngivna intervall använder du absoluta referenser istället.
Exakt matchning VLOOKUP är långsam
När du använder VLOOKUP i "exakt matchningsläge" på en stor uppsättning data kan det verkligen sakta ner beräkningstiden i ett kalkylblad. Med exempelvis 50 000 poster eller 100 000 poster kan beräkningen ta minuter.
Exakt matchning ställs in genom att ange FALSE eller noll som det fjärde argumentet:
=VLOOKUP(val,data,col,FALSE)
Anledningen till att VLOOKUP i detta läge är långsam är att den måste kontrollera varje enskild post i datamängden tills en matchning hittas. Detta kallas ibland linjär sökning.
Ungefärlig matchning VLOOKUP är mycket snabb
I ungefärligt matchningsläge är VLOOKUP extremt snabb. För att använda VLOOKUP med ungefärlig matchning måste du sortera dina data efter den första kolumnen (uppslagskolumnen) och sedan ange SANT för det fjärde argumentet:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP är som standard sant, vilket är en läskig standard, men det är en annan historia).
Med mycket stora datamängder kan byte till ungefär matchande VLOOKUP innebära en dramatisk hastighetsökning.
Så, no-brainer, eller hur? Sortera bara data, använd ungefärlig matchning så är du klar.
Inte så snabbt (heh).
Problemet med VLOOKUP i "ungefärlig matchning" -läge är detta: VLOOKUP visar inte ett fel om uppslagsvärdet inte finns. Värre är att resultatet kan se helt normalt ut, även om det är helt fel (se exempel). Inte något du vill förklara för din chef.
Lösningen är att använda VLOOKUP två gånger, båda gångerna i ungefärligt matchningsläge:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Förklaring
Den första instansen av VLOOKUP letar bara upp uppslagsvärdet ( id i detta exempel):
=IF(VLOOKUP(id,data,1,TRUE)=id
och returnerar SANT endast när uppslagsvärdet hittas. I så fall
kör formeln VLOOKUP igen i ungefärligt matchningsläge för att hämta ett värde från den tabellen:
VLOOKUP(id,data,col,TRUE)
Det finns ingen risk för att ett sökningsvärde saknas, eftersom den första delen av formeln redan har kontrollerats för att se till att den finns där.
Om sökningsvärdet inte hittas körs "värde om FALSK" -delen av IF-funktionen och du kan returnera vilket värde du vill. I det här exemplet använder vi NA () vi returnerar ett # N / A-fel, men du kan också returnera ett meddelande som "Missing" eller "Not found".
Kom ihåg: du måste sortera data efter sökvärde för att detta trick ska fungera.
