När du använder VLOOKUP -formeln i Excel kan du ibland få det fula #N/A -felet. Detta händer när din formel inte kan hitta uppslagsvärdet.
I den här självstudien kommer jag att visa dig olika sätt att använda IFERROR med VLOOKUP för att hantera dessa #N/A -fel som dyker upp i ditt kalkylblad.
Genom att använda kombinationen IFERROR med VLOOKUP kan du visa något meningsfullt istället för #N/A -felet (eller något annat fel för den delen).
Innan vi går in på detaljer om hur du använder denna kombination, låt oss först snabbt gå igenom IFERROR -funktionen och se hur den fungerar.
IFERROR -funktionen förklaras
Med IFERROR -funktionen kan du ange vad som ska hända om en formel eller en cellreferens returnerar ett fel.
Här är syntaxen för IFERROR -funktionen.
= IFERROR (värde, värde_if_fel)
- värde - detta är argumentet som kontrolleras för felet. I de flesta fall är det antingen en formel eller en cellreferens. När du använder VLOOKUP med IFERROR är VLOOKUP -formeln detta argument.
- value_if_error - detta är värdet som returneras om det finns ett fel. Följande feltyper utvärderades: #N/A, #REF !, #DIV/0 !, #VÄRDE !, #NUM !, #NAMN ?, och #NULL !.
Möjliga orsaker till VLOOKUP Retur a #N/A Error
VLOOKUP -funktionen kan returnera ett #N/A -fel på grund av någon av följande orsaker:
- Sökvärdet hittas inte i uppslagsmatrisen.
- Det finns ett ledande, bakre eller dubbla mellanslag i uppslagsvärdet (eller i tabellmatrisen).
- Det finns ett stavfel i uppslagsvärdet eller värdena i uppslagsmatrisen.
Du kan hantera alla dessa felorsaker med kombinationen IFERROR och VLOOKUP. Du bör dock hålla utkik efter orsak #2 och #3, och korrigera dessa i källdata istället för att låta IFERROR hantera dessa.
Obs: IFERROR behandlar ett fel oavsett vad som orsakade det. Om du bara vill behandla de fel som orsakas av att VLOOKUP inte kan hitta uppslagsvärdet, använd IFNA istället. Det kommer att se till att andra fel än #N/A inte behandlas och du kan undersöka dessa andra fel.
Du kan behandla ledande, bakre och dubbla mellanslag med TRIM -funktionen.
Ersättning av VLOOKUP #N/A -fel med meningsfull text
Anta att du har en dataset som visas nedan:
Som du kan se att VLOOKUP -formeln returnerar ett fel eftersom sökvärdet inte finns i listan. Vi letar efter poängen för Glen, som inte finns i tabellen över poäng.
Även om detta är en mycket liten dataset kan du få enorma datamängder där du måste kontrollera förekomsten av många objekt. För varje fall när värdet inte hittas får du ett #N/A -fel.
Här är formeln du kan använda för att få något meningsfullt istället för #N/A -felet.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Hittades inte")
Ovanstående formel returnerar texten "Hittades inte" istället för #N/A -felet. Du kan också använda samma formel för att returnera tom, noll eller annan meningsfull text.
Häckande VLOOKUP med IFERROR -funktion
Om du använder VLOOKUP och din uppslagstabell är fragmenterad på samma kalkylblad eller olika kalkylblad måste du kontrollera VLOOKUP -värdet genom alla dessa tabeller.
Till exempel, i datamängden som visas nedan, finns det två separata tabeller med elevnamn och poäng.
Om jag måste hitta poängen för Grace i denna dataset måste jag använda VLOOKUP -funktionen för att kontrollera den första tabellen, och om värdet inte finns i det, kontrollera sedan den andra tabellen.
Här är den kapslade IFERROR -formeln jag kan använda för att leta efter värdet:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
Använda VLOOKUP med IF och ISERROR (versioner före Excel 2007)
IFERROR -funktionen introducerades i Excel 2007 för Windows och Excel 2016 i Mac.
Om du använder tidigare versioner fungerar IFERROR -funktionen inte i ditt system.
Du kan replikera funktionen för IFERROR -funktionen genom att använda kombinationen av IF -funktionen och ISERROR -funktionen.
Låt mig snabbt visa dig hur du använder kombinationen IF och ISERROR istället för IFERROR.
I exemplet ovan kan du istället för att använda IFERROR också använda formeln som visas i cell B3:
= OM (FEL (A3), ”Hittades inte”, A3)
ISERROR -delen av formeln söker efter felen (inklusive #N/A -felet) och returnerar TRUE om ett fel hittas och FALSE om inte.
- Om det är SANT (vilket betyder att det finns ett fel) returnerar IF -funktionen det angivna värdet ("Hittades inte" i det här fallet).
- Om det är FALSKT (vilket betyder att det inte finns något fel) returnerar IF -funktionen det värdet (A3 i exemplet ovan).
IFERROR mot IFNA
IFERROR behandlar alla typer av fel medan IFNA endast behandlar #N/A -felet.
När du hanterar fel orsakade av VLOOKUP måste du se till att du använder rätt formel.
Använd IFERROR när du vill behandla alla typer av fel. Nu kan ett fel orsakas av olika faktorer (till exempel fel formel, felstavat namnintervall, att inte hitta uppslagsvärdet och returnera felvärde från uppslagstabellen). Det spelar ingen roll för IFERROR och det skulle ersätta alla dessa fel med det angivna värdet.
Använd IFNA när du bara vill behandla #N/A -fel, som troligen orsakas av att VLOOKUP -formeln inte kan hitta uppslagsvärdet.
Du kan också hitta följande Excel -självstudier användbara:
- Hur man gör VLOOKUP skiftlägeskänslig.
- VLOOKUP Vs. INDEX/MATCH - Debatten slutar här!
- Använd VLookup för att få det sista numret i en lista i Excel.
- Hur man använder VLOOKUP med flera kriterier
- #NAME -fel i Excel - vad som orsakar det och hur man åtgärdar det!
