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 eenvoudiger en handiger te gebruiken zijn dan hun voorgangers.
Stel dat u een lijst met locatienummers van kantoren hebt en dat u wilt weten welke werknemers zich in elk kantoor bevinden. Het werkblad is enorm groot, dus u zou kunnen denken dat het een lastige taak is. Het is eigenlijk heel eenvoudig met een opzoekfunctie.
De functies VERT.ZOEKEN en HORIZ.ZOEKEN zijn samen met INDEX en VERGELIJKEN enkele van de handigste functies in Excel.
Opmerking
De functie Wizard Opzoeken is niet meer beschikbaar in Excel.
Hier volgt 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 nodig heeft om te kunnen werken. Bij VERT.ZOEKEN is dit eerste argument de waarde die u zoekt. Dit argument kan een celverwijzing of een vaste waarde zijn, zoals 'splinter' of 21.000. Het tweede argument is het cellenbereik C2-:E7 waarin u wilt zoeken naar de waarde die u zoekt. Het derde argument is de kolom in dit celbereik die de gezochte waarde bevat.
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. U beschikt dus over meer flexibiliteit als u het vierde argument leeg laat of WAAR invoert.
In dit voorbeeld ziet u hoe de functie werkt. Wanneer u een waarde invoert in cel B2 (het eerste argument), doorzoekt VERT.ZOEKEN de cellen in het bereik C2-E7 (tweede argument) en wordt de dichtstbijzijnde niet-geheel exacte overeenkomst geretourneerd uit de derde kolom in het bereik, kolom E (derde argument).
Het vierde argument is leeg, dus wordt met de functie een niet-geheel exacte overeenkomst geretourneerd. 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.
Als u vertrouwd bent met VERT.ZOEKEN, is de functie HORIZ.ZOEKEN al even gemakkelijk te gebruiken. U voert dezelfde argumenten in, maar hierbij wordt gezocht in rijen in plaats van kolommen.
INDEX en VERGELIJKEN gebruiken in plaats van VERT.ZOEKEN
Er zijn bepaalde beperkingen aan het gebruik van VERT.ZOEKEN: met de functie VERT.ZOEKEN kan alleen een waarde van links naar rechts worden opgezocht. Dit betekent dat de kolom met de waarde die u zoekt, altijd links van de kolom met de retourwaarde moet staan. Als uw spreadsheet niet zo is gemaakt, moet u VERT.ZOEKEN niet gebruiken. Gebruik in plaats daarvan een 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.
Probeer het eens
Als u met opzoekfuncties wilt experimenteren voordat u ze op uw eigen gegevens toepast, kunt u deze voorbeeldgegevens gebruiken.
Voorbeeld van VERT.ZOEKEN in de praktijk
Kopieer de volgende gegevens naar een lege spreadsheet.
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 INDEX en VERGELIJKEN
In dit laatste voorbeeld worden de functies INDEX en VERGELJKEN samen gebruikt om het oudste factuurnummer en de bijbehorende datum op te halen voor elk van de vijf plaatsen. 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 |
Zie ook
Snelzoekkaart: Geheugensteun voor VERT.zoeken
Zoek- en verwijzingsfuncties (overzicht)
Het argument tabelmatrix gebruiken in de functie VERT.ZOEKEN