Tip: Probeer de nieuwe functies XLOOKUP en XMATCH te gebruiken, verbeterde versies van de functies die in dit artikel worden beschreven. Deze nieuwe functies werken in elke richting en retourneren standaard exacte overeenkomsten, waardoor ze gemakkelijker en handiger te gebruiken zijn dan hun voorgangers.
Stel dat u een lijst met kantoorlocatienummers hebt en dat u moet weten welke werknemers zich in elk kantoor bevinden. Het werkblad is enorm, dus u denkt misschien dat het een uitdagende taak is. Het is eigenlijk heel eenvoudig te doen met een opzoekfunctie.
De functies VERT.ZOEKEN en HORIZ.ZOEKEN , samen met INDEX en VERGELIJKEN, zijn enkele van de nuttigste functies in Excel.
Opmerking: De functie wizard Opzoeken is niet meer beschikbaar in Excel.
Hier volgt een voorbeeld van het gebruik van VERT.ZOEKEN.
=VERT.ZOEKEN(B2;C2:E7;3;WAAR)
In dit voorbeeld is B2 het eerste argument: een element van gegevens dat de functie moet werken. Voor VERT.ZOEKEN is dit eerste argument de waarde die u wilt zoeken. Dit argument kan een celverwijzing zijn of een vaste waarde zoals 'smith' of 21.000. Het tweede argument is het celbereik C2-:E7 waarin u naar de gewenste waarde wilt zoeken. Het derde argument is de kolom in dat celbereik 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 laat of WAAR invoert, krijgt 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 (tweede argument) en retourneert de dichtstbijzijnde benadering van de derde kolom in het bereik, kolom E (derde argument).
Het vierde argument is leeg, dus de functie retourneert een overeenkomst bij benadering. 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 net zo eenvoudig te gebruiken. U voert dezelfde argumenten in, maar zoekt in rijen in plaats van kolommen.
INDEX en VERGELIJKEN gebruiken in plaats van VERT.ZOEKEN
Er gelden bepaalde beperkingen bij het gebruik van VERT.ZOEKEN: de functie VERT.ZOEKEN kan alleen een waarde van links naar rechts opzoeken. Dit betekent dat de kolom met de waarde die u zoekt, zich altijd links van de kolom met de retourwaarde moet bevinden. Als uw spreadsheet niet op deze manier is gebouwd, gebruikt u 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.
Zie het artikel https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ door Bill Jelen, Microsoft MVP voor meer voorbeelden van het gebruik van INDEX en VERGELIJKEN in plaats van VERT.ZOEKEN.
Probeer het eens
Als u wilt experimenteren met opzoekfuncties voordat u ze met uw eigen gegevens probeert, 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 |
HLOOKUP-voorbeeld
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 |
INDEX- en VERGELIJKEN-voorbeelden
In dit laatste voorbeeld worden de functies INDEX en VERGELIJKEN samen gebruikt om het vroegste 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 |
Amsterdam |
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 |
Amsterdam |
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 |
Amsterdam |
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 |
Amsterdam |
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 |
Amsterdam |
27-4-12 |
|
|
3350 |
Amsterdam |
28-4-12 |
|
|
3390 |
Amsterdam |
1-5-12 |
|
|
3441 |
Amsterdam |
2-5-12 |
|
|
3517 |
Amsterdam |
8-5-12 |
|
|
3124 |
Assen |
9-4-12 |
|
|
3155 |
Assen |
11-4-12 |
|
|
3177 |
Assen |
19-4-12 |
|
|
3357 |
Assen |
28-4-12 |
|
|
3492 |
Assen |
6-5-12 |
|
|
3316 |
Den Bosch |
25-4-12 |
|
|
3346 |
Den Bosch |
28-4-12 |
|
|
3372 |
Den Bosch |
1-5-12 |
|
|
3414 |
Den Bosch |
1-5-12 |
|
|
3451 |
Den Bosch |
2-5-12 |
|
|
3467 |
Den Bosch |
2-5-12 |
|
|
3474 |
Den Bosch |
4-5-12 |
|
|
3490 |
Den Bosch |
5-5-12 |
|
|
3503 |
Den Bosch |
85-12 |
|
|
3151 |
Nijmegen |
9-4-12 |
|
|
3438 |
Nijmegen |
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 |
