Waarden opzoeken met VERT.ZOEKEN, INDEX of VERGELIJKEN

Tip: Gebruik de nieuwe functies van XLOOKUP en XMATCH , verbeterde versies van de functies die in dit artikel worden beschreven. Deze nieuwe functies werken in een willekeurige manier en geven standaard exacte overeenkomsten weer, zodat u ze eenvoudiger en gebruiksvriendelijker kunt gebruiken dan de voorafgaande taken.

Stel dat u een lijst met kantoorlocatie nummers hebt en u wilt weten welke werknemers deel uitmaken van elk kantoor. De spreadsheet is enorm, zodat u denkt dat het een onware taak is. Met een opzoekfunctie kunt u eigenlijk heel gemakkelijk.

De functies VERT . zoeken en HORIZ . zoeken, samen met index en vergelijken,zijn enkele van de meest nuttige functies in Excel.

Opmerking: De functie van de wizard Opzoeken is niet langer beschikbaar in Excel.

Hier ziet u een voorbeeld van hoe u VERT. zoeken gebruikt.

=VERT.ZOEKEN(B2;C2:E7;3;WAAR)

In dit voorbeeld is B2 het eerste argument: een gegevenselement dat de functie moet werken. Voor Vert. zoeken is het eerste argument de waarde die u wilt zoeken. Dit argument kan bestaan uit een celverwijzing of een vaste waarde zoals ' Smit ' of 21.000. Het tweede argument is het cellenbereik, C2-: E7, waarnaar u wilt zoeken naar de waarde die u wilt zoeken. Het derde argument is de kolom in het cellenbereik met de waarde die u zoekt.

Het vierde argument is optioneel. Voer waar of ONWAAR in. Als u WAAR invoert of het argument leeg laat, wordt met de functie een niet-geheel exacte overeenkomst geretourneerd van de opgegeven waarde in het eerste argument. Als u ONWAAR invoert, komt de functie overeen met de waarde die is opgegeven via het eerste argument. Met andere woorden, als u het vierde argument leeg wilt laten, of als u waar invoert, biedt u meer flexibiliteit.

In dit voorbeeld ziet u hoe de functie werkt. Wanneer u een waarde invoert in cel B2 (het eerste argument), zoekt Vert. zoeken de cellen in het bereik C2: E7 (2e argument) en wordt het dichtstbijzijnde geschatte resultaat van de derde kolom in het bereik, kolom E (3e argument) geretourneerd.

Een veelvoorkomend gebruik van de functie VERT.ZOEKEN

Het vierde argument is leeg, dus de functie retourneert een niet-geheel exacte overeenkomst. Als dit niet het geval zou zijn, zou u een van de waarden in kolom C of D moeten invoeren om een resultaat te verkrijgen.

Wanneer u vertrouwd bent met VERT. zoeken, is de functie HORIZ. zoeken heel gemakkelijk te gebruiken. U hoeft dezelfde argumenten in te voeren, maar in rijen wordt in plaats van kolommen gezocht.

INDEX en vergelijken gebruiken in plaats van Vert. zoeken

Er gelden bepaalde beperkingen bij het gebruik van Vert. zoeken: met de functie VERT. zoeken kunt u een waarde alleen van links naar rechts opzoeken. Dit betekent dat de kolom met de waarde die u wilt opzoeken, altijd aan de linkerkant van de kolom met de retourwaarde moet staan. Als uw spreadsheet niet op deze manier is gebouwd, maakt u de functie VERT. zoeken niet. Gebruik in plaats daarvan de combinatie van de functies INDEX en vergelijken.

In het volgende voorbeeld wordt een kleine lijst getoond waar de waarde die we zoeken, Chicago, zich niet in de meest linkse kolom bevindt. We kunnen VERT.ZOEKEN dus niet gebruiken. In plaats daarvan gebruiken we de functie VERGELIJKEN voor het vinden van Chicago in het bereik B1-B11. Deze wordt gevonden in rij 4. Vervolgens wordt door INDEX deze waarde gebruikt als het opzoekargument en wordt de bevolking van Chicago gevonden in de 4e kolom (kolom D). De gebruikte formule wordt weergegeven in cel A14.

INDEX en VERGELIJKEN gebruiken om een waarde te zoeken

Voor meer voorbeelden van het gebruik van INDEX en vergelijken in plaats van Vert. zoeken raadpleegt u het artikel https://www.MrExcel.com/Excel-tips/Excel-VLookup-index-match/ met factuur Jelen, Microsoft MVP.

Probeer het eens

Als u wilt experimenteren met opzoekfuncties voordat u ze uitprobeert met uw eigen gegevens, vindt u hier enkele voorbeeldgegevens.

Voorbeeld van Vert. zoeken op het werk

Kopieer de volgende gegevens naar een leeg werkblad.

Tip: Voordat u de gegevens in Excel plakt, stelt u de kolombreedte voor de kolommen A tot en met C in op 250 pixels en klikt u op Terugloop (tabblad Start, groep Uitlijning).

Dichtheid

Viscositeit

Temperatuur

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Formule

Beschrijving

Resultaat

=VERT.ZOEKEN(1;A2:C10;2)

Met behulp van een benadering van de waarde wordt naar de waarde 1 gezocht in kolom A, wordt de grootste waarde kleiner dan of gelijk aan 1 in kolom A gevonden, in dit geval 0,946, en vervolgens wordt de waarde geretourneerd uit kolom B in dezelfde rij.

2,17

=VERT.ZOEKEN(1;A2:C10;3;WAAR)

Met behulp van een benadering van de waarde wordt naar de waarde 1 gezocht in kolom A, wordt de grootste waarde kleiner dan of gelijk aan 1 in kolom A gevonden, in dit geval 0,946, en vervolgens wordt de waarde geretourneerd uit kolom C in dezelfde rij.

100

=VERT.ZOEKEN(0,7;A2:C10;3;FALSE)

Met behulp van een exacte waarde wordt naar de waarde 0,7 gezocht in kolom A. Aangezien kolom A geen exacte overeenkomst bevat, levert dit een fout op.

#N/B

=VERT.ZOEKEN(0,1;A2:C10;2;WAAR)

Met behulp van een benadering van de waarde wordt naar de waarde 0,1 gezocht in kolom A. Aangezien 0,1 kleiner is dan de kleinste waarde in kolom A, levert dit een fout op.

#N/B

=VERT.ZOEKEN(2;A2:C10;2;WAAR)

Met behulp van een benadering van de waarde wordt naar de waarde 2 gezocht in kolom A, wordt de grootste waarde kleiner dan of gelijk aan 2 in kolom A gevonden, in dit geval 1,29, en vervolgens wordt de waarde geretourneerd uit kolom B in dezelfde rij.

1,71

Voorbeeld van HORIZ. zoeken

Kopieer alle cellen in deze tabel en plak ze in cel A1 van een leeg werkblad in Excel.

Tip: Voordat u de gegevens in Excel plakt, stelt u de kolombreedte voor de kolommen A tot en met C in op 250 pixels en klikt u op Terugloop (tabblad Start, groep Uitlijning).

Assen

Lagers

Bouten

4

4

9

5

7

10

6

8

11

Formule

Beschrijving

Resultaat

=HORIZ.ZOEKEN("Assen"; A1:C4; 2; WAAR)

Hiermee wordt gezocht naar de waarde 'Assen' in rij 1 en wordt de waarde van rij 2 in dezelfde kolom als resultaat gegeven (kolom A).

4

=HORIZ.ZOEKEN("Lagers"; A1:C4; 3; ONWAAR)

Hiermee wordt gezocht naar de waarde 'Lagers' in rij 1 en wordt de waarde van rij 3 in dezelfde kolom als resultaat gegeven (kolom B).

7

=HORIZ.ZOEKEN("B"; A1:C4; 3; WAAR)

Hiermee wordt gezocht naar de waarde 'B' in rij 1 en wordt de waarde van rij 3 in dezelfde kolom als resultaat gegeven. Omdat geen exacte overeenkomst voor 'B' wordt gevonden, wordt de volgende grootste waarde gebruikt die kleiner is dan 'B': 'Assen' in kolom A.

5

=HORIZ.ZOEKEN("Bouten"; C1:C4; 4)

Hiermee wordt gezocht naar de waarde 'Bouten' in rij 1 en wordt de waarde van rij 4 in dezelfde kolom als resultaat gegeven (kolom C).

11

=HORIZ.ZOEKEN(3; {1,2,3;"a","b","c";"d","e","f"}; 2; WAAR)

Hiermee wordt gezocht naar het getal 3 in de uit drie rijen bestaande matrixconstante en wordt de waarde van rij 2 in dezelfde kolom (in dit geval de derde kolom) als resultaat gegeven. Er zijn drie rijen met waarden in de matrixconstante en de rijen worden met behulp van een puntkomma (;) van elkaar gescheiden. Omdat 'c' in rij 2 wordt gevonden en de waarde zich bevindt in dezelfde kolom als 3, wordt 'c' als resultaat gegeven.

c

Voorbeelden van indexen en overeenkomsten

In dit voorbeeld worden de functies INDEX en vergelijken gebruikt om het eerstvolgende factuurnummer en de bijbehorende datum voor elk van de vijf steden te retourneren. Aangezien de datum wordt opgeleverd als getal, wordt de functie TEKST gebruikt om het getal op te maken als een datum. De functie INDEX gebruikt het resultaat van de functie VERGELIJKEN als argument. De combinatie van de functies INDEX en VERGELIJKEN wordt in elke formule tweemaal gebruikt: eerst om het factuurnummer op te leveren en daarna om de datum op te leveren.

Kopieer alle cellen in deze tabel en plak ze in cel A1 van een leeg werkblad in Excel.

Tip: Voordat u de gegevens in Excel plakt, stelt u de kolombreedte voor de kolommen A tot en met C in op 250 pixels en klikt u op Terugloop (tabblad Start, groep Uitlijning).

Factuur

Plaats

Factuurdatum

Oudste factuur per plaats, met datum

3115

Atlanta

7-4-12

="Amsterdam = "&INDEX($A$2:$C$33,VERGELIJKEN("Amsterdam",$B$2:$B$33,0),1)& ", Factuurdatum: " & TEKST(INDEX($A$2:$C$33,VERGELIJKEN("Amsterdam",$B$2:$B$33,0),3),"d-m-jj")

3137

Atlanta

9-4-12

="Assen = "&INDEX($A$2:$C$33,VERGELIJKEN("Assen",$B$2:$B$33,0),1)& ", Factuurdatum: " & TEKST(INDEX($A$2:$C$33,VERGELIJKEN("Assen",$B$2:$B$33,0),3),"d-m-jj")

3154

Atlanta

11-4-12

="Den Bosch = "&INDEX($A$2:$C$33,VERGELIJKEN("Den Bosch",$B$2:$B$33,0),1)& ", Factuurdatum: " & TEKST(INDEX($A$2:$C$33,VERGELIJKEN("Den Bosch",$B$2:$B$33,0),3),"d-m-jj")

3191

Atlanta

21-4-12

="Nijmegen = "&INDEX($A$2:$C$33,VERGELIJKEN("Nijmegen",$B$2:$B$33,0),1)& ", Factuurdatum: " & TEKST(INDEX($A$2:$C$33,VERGELIJKEN("Nijmegen",$B$2:$B$33,0),3),"d-m-jj")

3293

Amsterdam

25-4-12

="Tilburg = "&INDEX($A$2:$C$33,VERGELIJKEN("Tilburg",$B$2:$B$33,0),1)& ", Factuurdatum: " & TEKST(INDEX($A$2:$C$33,VERGELIJKEN("Tilburg",$B$2:$B$33,0),3),"d-m-jj")

3331

Atlanta

27-4-12

3350

Atlanta

28-4-12

3390

Atlanta

1-5-12

3441

Atlanta

2-5-12

3517

Amsterdam

8-5-12

3124

Austin

9-4-12

3155

Austin

11-4-12

3177

Austin

19-4-12

3357

Austin

28-4-12

3492

Assen

6-5-12

3316

Dallas

25-4-12

3346

Dallas

28-4-12

3372

Dallas

1-5-12

3414

Dallas

1-5-12

3451

Den Bosch

2-5-12

3467

Dallas

2-5-12

3474

Dallas

4-5-12

3490

Dallas

5-5-12

3503

Den Bosch

85-12

3151

New Orleans

9-4-12

3438

New Orleans

2-5-12

3471

Nijmegen

4-5-12

3160

Tilburg

18-4-12

3328

Tilburg

26-4-12

3368

Tilburg

29-4-12

3420

Tilburg

1-5-12

3501

Tilburg

6-5-12

Zie ook

Snelzoekkaart:

Zoek-en verwijzingsfuncties voor Vert. zoeken (overzicht)

gebruik het argument table_array in de functie VERT

Uw Office-vaardigheden uitbreiden
Training verkennen
Als eerste nieuwe functies krijgen
Deelnemen aan Office Insiders

Was deze informatie nuttig?

Bedankt voor uw feedback.

Hartelijk dank voor uw feedback! Het lijkt ons een goed idee om u in contact te brengen met een van onze Office-ondersteuningsagenten.

×