Exakt kontra ungefärlig matchning

Med vår pristabell (A100 Apple 0.50, B200 Banana 0.30, C300 Cherry 1.20) fungerar en exakt uppslagning av en befintlig kod perfekt:

Returnerar 1.20. Men om du söker efter en kod som inte finns, till exempel "Z999", får du #N/A. Felet är faktiskt användbart: det visar att posten verkligen saknas i stället för att returnera en felaktig granne.

En ungefärlig matchning hittar det största värdet som är mindre än eller lika med ditt uppslagsvärde. Den är avsedd för intervall och nivåer, inte för exakta ID:n.

Det klassiska användningsområdet är en nivåtabell: skatteintervall, fraktintervall, betygsgränser eller mängdrabatter, där ett värde hamnar någonstans mellan två tröskelvärden.

TRUE medför en viktig regel, som vi går igenom i nästa avsnitt.

För att en ungefärlig matchning ska fungera måste den första kolumnen vara sorterad i stigande ordning (från minst till störst). VLOOKUP går nedåt i kolumnen och stannar vid det sista värdet som inte överstiger ditt uppslagsvärde.

Om kolumnen inte är sorterad returnerar TRUE oförutsägbara och felaktiga svar utan att visa något fel. Det här tysta felet är anledningen till att många undviker TRUE om de inte specifikt behöver intervallindelning.

Anta att en betygstabell finns i A1:B5 och är sorterad stigande efter lägsta poäng:

• 0 = F
• 60 = D
• 70 = C
• 80 = B
• 90 = A

Poängen 76 ska returnera C, eftersom 76 hamnar i intervallet 70–79. Formeln använder TRUE för att hitta det högsta gränsvärdet som inte överstiger 76:

Med uppslagsvärdet 76 och TRUE läser VLOOKUP de sorterade gränsvärdena uppifrån och ned: 0, 60, 70, 90 … Funktionen jämför vart och ett av dem.

• 0 är mindre än eller lika med 76: fortsätt
• 60 är mindre än eller lika med 76: fortsätt
• 70 är mindre än eller lika med 76: fortsätt
• 90 är större än 76: stoppa

Funktionen går tillbaka till den sista giltiga raden (70) och returnerar dess intervall: C. En ungefärlig matchning är i praktiken en uppslagning mellan tröskelvärden.

Om du helt utelämnar det fjärde argumentet blir standardvärdet för VLOOKUP TRUE (ungefärlig matchning). Det överraskar många som förväntar sig en exakt matchning.

En formel som =VLOOKUP(A2, Data!A:B, 2) i en osorterad lista kan tyst returnera ett felaktigt värde. En säker vana är att alltid skriva FALSE, såvida du inte medvetet gör en intervallmatchning i en sorterad tabell.

Här är skillnaden i korthet:

• FALSE / exakt: tabellen behöver inte vara sorterad, ett saknat värde returnerar #N/A, bäst för ID:n och koder
• TRUE / ungefärlig: tabellen måste vara sorterad stigande, returnerar aldrig #N/A för värden inom intervallet, bäst för nivåer och intervall

Välj utifrån frågan du försöker besvara: ”Finns den här exakta posten?” använder FALSE, medan ”Vilket intervall hamnar det här i?” använder TRUE.

En vikt i D2 behöver en fraktkostnad från en sorterad intervalltabell i A2:B6 (trösklarna 0, 1, 5, 10 och 20 kg). En ungefärlig matchning väljer rätt nivå:

Om D2 är 7 hamnar värdet i intervallet för 5 kg och returnerar kostnaden för den nivån. Ändra vikten så uppdateras nivån direkt, utan att du behöver en lista över varje möjlig vikt.

Det finns också en subtil prestandaaspekt. I mycket stora, sorterade tabeller kan en ungefärlig matchning (TRUE) vara snabbare, eftersom kalkylprogrammet snabbt kan hoppa genom sorterade värden i stället för att söka igenom varje rad.

Men snabbhet går aldrig före korrekthet. Om dina data inte är sorterade, eller om du behöver exakta ID:n, ska du alltid välja FALSE. Ett snabbt felaktigt svar är sämre än ett något långsammare korrekt svar. I moderna kalkylprogram och vid typiska tabellstorlekar märks skillnaden sällan, så välj FALSE som standard för säkerhets skull.

Viktiga slutsatser:

• FALSE = exakt matchning, det går bra om tabellen är osorterad, saknade värden returnerar #N/A
• TRUE = ungefärlig matchning, den första kolumnen måste vara sorterad stigande och funktionen hittar det största värdet som inte överstiger ditt mål
• Om du utelämnar argumentet blir standardvärdet TRUE, så ange det alltid
• Använd exakt matchning för ID:n och koder, och ungefärlig matchning för nivåer och intervall

Nästa steg är att vända riktningen och söka över rader med HLOOKUP.