Når du bruker VLOOKUP -formelen i Excel, kan du noen ganger ende opp med den stygge #N/A -feilen. Dette skjer når formelen din ikke finner oppslagsverdien.
I denne opplæringen vil jeg vise deg forskjellige måter å bruke IFERROR med VLOOKUP for å håndtere disse #N/A -feilene som dukker opp i regnearket.
Ved å bruke kombinasjonen IFERROR med VLOOKUP kan du vise noe meningsfylt i stedet for #N/A -feilen (eller en annen feil for den saks skyld).
Før vi går inn på detaljer om hvordan du bruker denne kombinasjonen, la oss først gå gjennom IFERROR -funksjonen og se hvordan den fungerer.
IFERROR -funksjon forklart
Med IFERROR -funksjonen kan du angi hva som skal skje i tilfelle en formel eller en cellereferanse returnerer en feil.
Her er syntaksen til IFERROR -funksjonen.
= IFERROR (verdi, verdi_if_feil)
- verdi - dette er argumentet som er sjekket for feilen. I de fleste tilfeller er det enten en formel eller en cellereferanse. Når du bruker VLOOKUP med IFERROR, er VLOOKUP -formelen dette argumentet.
- value_if_error - dette er verdien som returneres hvis det er en feil. Følgende feiltyper evalueres: #N/A, #REF !, #DIV/0 !, #VALUE!, #NUM !, #NAME ?, og #NULL !.
Mulige årsaker til VLOOKUP Returnering av #N/A -feil
VLOOKUP -funksjonen kan returnere en #N/A -feil på grunn av en av følgende årsaker:
- Oppslagsverdien finnes ikke i oppslagsoppsettet.
- Det er et ledende, etterfølgende eller dobbelt mellomrom i oppslagsverdien (eller i tabelloppsettet).
- Det er en stavefeil i oppslagsverdien eller verdiene i oppslagsoppsettet.
Du kan håndtere alle disse årsakene til feil med kombinasjonen IFERROR og VLOOKUP. Du bør imidlertid holde øye med årsak 2 og 3, og korrigere disse i kildedataene i stedet for å la IFERROR håndtere disse.
Merk: IFERROR vil behandle en feil uavhengig av hva som forårsaket den. Hvis du bare vil behandle feilene forårsaket av at VLOOKUP ikke kan finne oppslagsverdien, bruker du IFNA i stedet. Det vil sikre at andre feil enn #N/A ikke blir behandlet, og du kan undersøke disse andre feilene.
Du kan behandle ledende, etterfølgende og doble mellomrom ved å bruke TRIM -funksjonen.
Erstatter VLOOKUP #N/A -feil med meningsfull tekst
Anta at du har et datasett som vist nedenfor:
Som du kan se at VLOOKUP -formelen returnerer en feil ettersom oppslagsverdien ikke er i listen. Vi ønsker å få poengsummen for Glen, som ikke er i tabellen over poeng.
Selv om dette er et veldig lite datasett, kan du få enorme datasett der du må kontrollere forekomsten av mange elementer. For hvert tilfelle når verdien ikke blir funnet, får du en #N/A -feil.
Her er formelen du kan bruke for å få noe meningsfylt i stedet for #N/A -feilen.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Not Found")
Formelen ovenfor returnerer teksten "Not Found" i stedet for #N/A -feilen. Du kan også bruke den samme formelen til å returnere blank, null eller annen meningsfull tekst.
Nesting VLOOKUP Med IFERROR -funksjon
Hvis du bruker VLOOKUP og oppslagstabellen er fragmentert på det samme regnearket eller forskjellige regneark, må du sjekke VLOOKUP -verdien gjennom alle disse tabellene.
For eksempel, i datasettet vist nedenfor, er det to separate tabeller med elevnavn og poengsummene.
Hvis jeg må finne poengsummen til Grace i dette datasettet, må jeg bruke VLOOKUP -funksjonen for å kontrollere den første tabellen, og hvis verdien ikke finnes i den, sjekk den andre tabellen.
Her er den nestede IFERROR -formelen jeg kan bruke til å se etter verdien:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
Bruke VLOOKUP med IF og ISERROR (Versjoner før Excel 2007)
IFERROR -funksjonen ble introdusert i Excel 2007 for Windows og Excel 2016 i Mac.
Hvis du bruker de tidligere versjonene, fungerer ikke IFERROR -funksjonen i systemet ditt.
Du kan replikere funksjonaliteten til IFERROR -funksjonen ved å bruke kombinasjonen av IF -funksjonen og ISERROR -funksjonen.
La meg raskt vise deg hvordan du bruker kombinasjonen IF og ISERROR i stedet for IFERROR.
I eksemplet ovenfor kan du i stedet for å bruke IFERROR også bruke formelen vist i celle B3:
= HVIS (FEIL (A3), "Ikke funnet", A3)
ISERROR -delen av formelen sjekker om feilene (inkludert #N/A -feilen) og returnerer TRUE hvis en feil blir funnet og FALSE hvis ikke.
- Hvis det er TRUE (som betyr at det er en feil), returnerer IF -funksjonen den angitte verdien ("Ikke funnet" i dette tilfellet).
- Hvis det er FALSE (som betyr at det ikke er noen feil), returnerer IF -funksjonen denne verdien (A3 i eksemplet ovenfor).
IFERROR mot IFNA
IFERROR behandler alle slags feil mens IFNA bare behandler #N/A -feilen.
Når du håndterer feil forårsaket av VLOOKUP, må du sørge for at du bruker riktig formel.
Bruk IFERROR når du vil behandle alle slags feil. Nå kan en feil skyldes ulike faktorer (for eksempel feil formel, feilstavet navngitt område, ikke finne oppslagsverdien og returnere feilverdi fra oppslagstabellen). Det ville ikke ha noen betydning for IFERROR, og det ville erstatte alle disse feilene med den angitte verdien.
Bruk IFNA når du bare vil behandle #N/A -feil, som mer sannsynlig er forårsaket av at VLOOKUP -formelen ikke kan finne oppslagsverdien.
Du kan også finne følgende Excel -opplæringsprogrammer nyttige:
- Slik gjør du VLOOKUP Case Sensitive.
- VLOOKUP Vs. INDEKS/MATCH - Debatten ender her!
- Bruk VLookup for å få det siste nummeret i en liste i Excel.
- Slik bruker du VLOOKUP med flere kriterier
- #NAME -feil i Excel - hva som forårsaker det og hvordan fikser jeg det!
