VERGELIJKEN in Excel: de positie van een waarde vinden

VERGELIJKEN beantwoordt de vraag: op welke plek staat deze waarde? Staat in A2:A6 de lijst Amsterdam, Rotterdam, Den Haag, Utrecht, Eindhoven, dan geeft =VERGELIJKEN("Utrecht";A2:A6;0) het getal 4. Utrecht is de vierde waarde in het bereik.

De positie telt vanaf het begin van het bereik, niet vanaf rij 1 van het werkblad. Begint je bereik in A2, dan is A2 positie 1.

VERGELIJKEN werkt in één kolom of één rij. Geef je een blok van meerdere kolommen en rijen op, dan krijg je #N/B. Hoofdletters maken niet uit: "utrecht" vindt ook "Utrecht". Bij exact zoeken kun je ook jokertekens gebruiken, zoals "Rot*" voor alles wat met Rot begint. Staat de waarde er meer dan eens in, dan krijg je de positie van de eerste.

Het laatste argument bepaalt hoe VERGELIJKEN zoekt. Dit is de belangrijkste valkuil van de functie.

• 0 zoekt een exacte overeenkomst. De lijst mag in elke volgorde staan. Dit gebruik je bijna altijd.
• 1 zoekt de grootste waarde die kleiner is dan of gelijk is aan de zoekwaarde. De lijst moet oplopend gesorteerd zijn. Dit is de standaard als je niets invult.
• -1 zoekt de kleinste waarde die groter is dan of gelijk is aan de zoekwaarde. De lijst moet aflopend gesorteerd zijn.

Omdat 1 de standaard is, gaat het vaak mis als je het argument weglaat. In een ongesorteerde lijst met namen krijg je dan een verkeerde positie of #N/B, terwijl de naam er gewoon in staat. Typ dus altijd ;0 als je een exacte positie wilt.

Vindt VERGELIJKEN de waarde niet, dan krijg je #N/B. Dat kun je gebruiken om te controleren of iets in een lijst voorkomt. Combineer VERGELIJKEN met ISGETAL:

• =ISGETAL(VERGELIJKEN(D2;A2:A50;0))

Dit geeft WAAR als de waarde in de lijst staat en ONWAAR als dat niet zo is. Wil je een leesbare tekst, zet het dan in ALS:

• =ALS(ISGETAL(VERGELIJKEN(D2;A2:A50;0));"Ja";"Nee")

Zo vergelijk je snel twee lijsten, bijvoorbeeld welke klanten van vorig jaar ook dit jaar hebben besteld, of welke artikelen uit een levering niet in je assortiment staan. Trek de formule naar beneden en zet de lijst vast met $, zoals $A$2:$A$50.

In de praktijk gebruik je VERGELIJKEN meestal als onderdeel van een grotere formule. De bekendste combinatie is met INDEX: VERGELIJKEN zoekt de positie en INDEX haalt de waarde op die plek uit een andere kolom.

• =INDEX(C2:C50;VERGELIJKEN(E2;A2:A50;0))

Je kunt VERGELIJKEN ook gebruiken om het kolomnummer van VERT.ZOEKEN te bepalen. Dan blijft de formule kloppen als er een kolom bij komt:

• =VERT.ZOEKEN(E2;A2:F50;VERGELIJKEN("Prijs";A1:F1;0);ONWAAR)

Excel zoekt het kopje Prijs in de eerste rij en gebruikt die positie als kolomindex. Schuift de kolom Prijs later op, dan schuift het nummer vanzelf mee.

In Microsoft 365 en Excel 2021 of nieuwer bestaat ook X.VERGELIJKEN. Die werkt hetzelfde, maar met betere standaardinstellingen. X.VERGELIJKEN zoekt standaard exact, dus je kunt de 0 niet vergeten. Daarnaast kan het van onder naar boven zoeken, en hoeft de lijst voor de dichtstbijzijnde kleinere of grotere waarde niet gesorteerd te zijn.

• =X.VERGELIJKEN(E2;A2:A50) geeft dezelfde positie als =VERGELIJKEN(E2;A2:A50;0)

Moet je bestand ook in oudere versies werken, gebruik dan VERGELIJKEN. In de Engelse Excel heten ze MATCH en XMATCH.