Waarom krijg ik #N/B?

Je hebt een lijst met klantnummers en namen, en in E2 staat =VERT.ZOEKEN(D2;A2:C5;2;ONWAAR). In D2 typ je 3053. Dat nummer staat in kolom A, je ziet het met je eigen ogen in rij 4, en toch toont E2 #N/B. Je typt het nummer opnieuw, je controleert de formule nog een keer, en er verandert niets.

Zo'n melding zegt dat de formule iets anders zoekt dan wat er in de tabel staat, ook als het er voor jou hetzelfde uitziet. Hieronder staat eerst wat #N/B betekent, dan vijf oorzaken met per oorzaak hoe je hem herkent, en daarna wanneer je de melding mag wegwerken en wanneer liever niet.

Wat #N/B betekent

#N/B is de Nederlandse foutwaarde voor niet beschikbaar; in een Engelstalig programma heet hij #N/A. Microsoft zegt het op de helppagina over #N/B zo: 'De fout #N/B geeft meestal aan dat een formule iets niet kan vinden waarnaar wordt gezocht.' Dezelfde pagina noemt X.ZOEKEN, VERT.ZOEKEN, HORIZ.ZOEKEN, ZOEKEN en VERGELIJKEN als de functies waar dat het vaakst gebeurt.

Wat de functie zoekt, is de zoekwaarde: het eerste deel tussen de haakjes. Bij =VERT.ZOEKEN(D2;A2:C5;2;ONWAAR) is dat wat er in D2 staat, en de functie zoekt het in de eerste kolom van A2:C5. Elke oorzaak hieronder komt erop neer dat die twee voor het programma niet gelijk zijn.

1. De waarde staat er echt niet in

De eenvoudigste oorzaak is ook de meest voorkomende. Microsoft schrijft dat de opzoekwaarde dan 'bijvoorbeeld niet in de brongegevens' bestaat. Een nieuw artikelnummer dat nog niet in de prijslijst staat, een klant die vorige maand is toegevoegd aan de ene lijst en niet aan de andere, een tikfout in de zoekcel.

Zoek de waarde op met de zoekfunctie van je programma, in de kolom waarin de formule zoekt. Vind je hem niet, dan klopt de melding en ligt het probleem in de lijst. Vind je hem wel, dan is het een van de vier oorzaken hierna.

2. Het ene is een getal, het andere tekst

Een nummer kan in een cel staan als getal of als tekst, en op het scherm zie je dat nauwelijks. Microsoft noemt het 'Verkeerde waardetypen', met als voorbeeld dat 'u VERT.ZOEKEN te laten verwijzen naar een getal, maar het brongegeven is opgeslagen als tekst.' Voor de functie is het getal 3053 een andere waarde dan de tekst 3053.

Vaak zie je het aan de uitlijning. Staat het ene nummer links in de cel en het andere rechts, dan zijn ze waarschijnlijk van een ander soort. Soms staat er een apostrof voor het nummer in de formulebalk, zoals '3053; die maakt er tekst van. Ook nummers die uit een ander systeem zijn gekopieerd, komen vaak als tekst binnen. Typ het nummer opnieuw zonder apostrof, of zet de hele kolom om naar getallen.

3. Er staat een spatie te veel

"Vlietdam" en "Vlietdam " met een spatie erachter zien er in een cel hetzelfde uit, maar zijn twee verschillende teksten. Microsoft noemt het 'Er is extra ruimte in de cellen' en wijst naar de functie SPATIES.WISSEN (TRIM), die spaties aan het begin en eind weghaalt.

Klik in de cel en zet de cursor achter de laatste letter. Springt hij nog een plek verder, dan staat daar een spatie. Bij een lange lijst is een hulpkolom handig: =SPATIES.WISSEN(A2) geeft de naam zonder spaties, en daarin laat je de formule zoeken.

4. De waarde staat niet in de eerste kolom

Deze geldt alleen voor VERT.ZOEKEN. Op de helppagina over VERT.ZOEKEN staat: 'Houd er rekening mee dat de opzoekwaarde altijd in de eerste kolom in het bereik van VERT.ZOEKEN moet staan om deze correct te laten werken.' Zoek je een plaatsnaam in een tabel die begint met een kolom nummers, dan kijkt de functie alleen naar die nummers en vindt hij de plaats nooit.

Een ander kolomnummer helpt daar niet, want dat bepaalt alleen uit welke kolom het antwoord komt. Laat het bereik beginnen bij de kolom waarin je zoekt, of gebruik X.ZOEKEN (XLOOKUP). Daar geef je de zoekkolom en de antwoordkolom los op: =X.ZOEKEN(D2;B2:B5;A2:A5) zoekt in kolom B en geeft wat in kolom A ernaast staat. De verschillen tussen de twee staan op X.ZOEKEN en VERT.ZOEKEN: wanneer gebruik je welke?

5. Het bereik is te kort

Een bereik als A2:C5 zonder dollartekens schuift mee als je de formule omlaag kopieert. Dat is hoe een relatieve verwijzing werkt, volgens de helppagina over verwijzingen: bij kopiëren wordt de verwijzing in de formule gewijzigd. Eén rij lager staat er A3:C6, twee rijen lager A4:C7, en de bovenste regels van de tabel vallen erbuiten. Wie daar naar zoekt, krijgt #N/B, terwijl de formule in de eerste rij gewoon werkte.

Hetzelfde gebeurt als je een nieuwe regel onder de tabel typt en het bereik bij de oude laatste rij ophoudt. Zet het bereik vast met dollartekens, zoals $A$2:$C$6, en laat het doorlopen tot de laatste regel. Wat een dollarteken precies vastzet, staat in Het dollarteken in een formule.

Geen #N/B, toch een verkeerd antwoord

Er is één fout die juist géén #N/B geeft. Laat je het laatste deel van VERT.ZOEKEN weg, dan zoekt de functie bij benadering. Microsoft schrijft op de pagina over VERT.ZOEKEN dat WAAR de standaardwaarde is als je niets opgeeft. Zoek je dan een code die niet in de lijst staat, dan krijg je de waarde uit een rij die er alfabetisch of in getal net onder ligt, zonder melding. Op de pagina over #N/B staat het voorbeeld van een peer die zo een verkeerde prijs krijgt. Zet daarom ONWAAR aan het eind als je een exacte overeenkomst zoekt; dan is #N/B de juiste uitkomst als de code ontbreekt.

X.ZOEKEN zoekt volgens de helppagina over X.ZOEKEN standaard exact. Daar hoef je dus niets toe te voegen.

Wanneer je #N/B mag wegwerken

Soms hoort een waarde te ontbreken, bijvoorbeeld bij een klant die nog geen bestelling heeft. Dan wil je een lege cel of een eigen tekst in plaats van een foutmelding. Microsoft raadt daarvoor 'foutafhandeling zoals ALS.FOUT' aan. Met =ALS.FOUT(VERT.ZOEKEN(D2;$A$2:$C$6;2;ONWAAR);"") toon je een lege cel, en bij X.ZOEKEN geef je als vierde deel een tekst op: =X.ZOEKEN(D2;A2:A6;B2:B6;"niet gevonden").

Doe dat pas als je weet waarom de waarde ontbreekt. ALS.FOUT vangt elke fout op, ook een spatie of een getal als tekst. De cel ziet er dan netjes uit terwijl er een klant ontbreekt die wel in de lijst stond.

Oefenen met opzoekformules

Skillido heeft een gratis test met korte vragen over formules, opzoeken en draaitabellen, met het blad bij elke vraag. In de training Spreadsheets: formules en draaitabellen gaan vierentwintig vragen over VERT.ZOEKEN en X.ZOEKEN, en bij een aantal daarvan is het goede antwoord dat de cel #N/B toont. Bij elk antwoord lees je waarom.