Hoe de Excel VERT.ZOEKEN-functie | te gebruiken Exceljet

VERT.ZOEKEN is een Excel-functie om gegevens uit een verticaal georganiseerde tabel op te halen. Opzoekwaarden moeten verschijnen in de eerste kolom van de tabel die is doorgegeven aan VERT.ZOEKEN. VERT.ZOEKEN ondersteunt geschatte en exacte overeenkomsten, en jokertekens (*?) Voor gedeeltelijke overeenkomsten.

V is voor verticaal

Het doel van VERT.ZOEKEN is om informatie op te halen uit een tabel die als volgt is georganiseerd:

Door het bestelnummer in kolom B als opzoekwaarde te gebruiken, kan VERT.ZOEKEN de klant-ID, het bedrag, de naam en de staat voor elke bestelling ophalen. Om bijvoorbeeld de klantnaam voor bestelling 1004 te krijgen, is de formule:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

Voor horizontale gegevens kunt u de HLOOKUP, INDEX en MATCH, of XLOOKUP.

VERT.ZOEKEN is gebaseerd op kolomnummers

Als je VERT.ZOEKEN gebruikt, stel je dan voor dat elke kolom in de tabel genummerd is, beginnend vanaf de linkerkant. Om een waarde uit een bepaalde kolom te halen, geeft u het juiste nummer op als de “kolomindex”. De kolomindex om de voornaam hieronder op te halen is bijvoorbeeld 2:

De achternaam en het e-mailadres kunnen worden opgehaald met kolommen 3 en 4:

=VLOOKUP(H3,B4:E13,2,FALSE) // first name=VLOOKUP(H3,B4:E13,3,FALSE) // last name=VLOOKUP(H3,B4:E13,4,FALSE) // email address

VERT.ZOEKEN ziet er alleen goed uit

VERT.ZOEKEN kan alleen naar rechts kijken. De gegevens die u wilt ophalen (resultaatwaarden) kunnen in elke kolom rechts van de opzoekwaarden verschijnen:

Als je moet de waarden aan de linkerkant opzoeken, zie INDEX en MATCH, of XLOOKUP.

Exacte en benaderende overeenkomsten

VERT.ZOEKEN heeft twee manieren om te matchen, exact en bij benadering. De naam van het argument dat het matchen bestuurt, is “range_lookup”. Dit is een verwarrende naam, omdat het iets te maken lijkt te hebben met celbereiken zoals A1: A10. In feite verwijst het woord “bereik” in dit geval naar “bereik van waarden” – wanneer bereik_opzoeken WAAR is, zal VERT.ZOEKEN overeenkomen met een bereik van waarden in plaats van een exacte waarde. Een goed voorbeeld hiervan is het gebruik van VERT.ZOEKEN om cijfers te berekenen.

Het is belangrijk om te begrijpen dat range_lookup standaard TRUE is, wat betekent dat VERT.ZOEKEN standaard geschatte overeenkomsten gebruikt, wat gevaarlijk kan zijn. Stel range_lookup in op FALSE om exacte overeenkomst af te dwingen:

Opmerking: u kunt ook nul (0) opgeven in plaats van FALSE voor een exacte overeenkomst.

Exacte overeenkomst

In de meeste gevallen wilt u VERT.ZOEKEN waarschijnlijk gebruiken in de modus voor exact zoeken. Dit is logisch als u een unieke sleutel heeft om als opzoekwaarde te gebruiken, bijvoorbeeld de filmtitel in deze gegevens:

De formule in H6 om Jaar te vinden, gebaseerd op een exacte overeenkomst met de filmtitel, is:

=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

Geschatte overeenkomst

In gevallen waarin u de beste overeenkomst wilt, niet noodzakelijk een exacte overeenkomst, wilt u de benaderingsmodus gebruiken. Hieronder willen we bijvoorbeeld een commissiepercentage opzoeken in de tabel G5: H10. De opzoekwaarden komen uit kolom C. In dit voorbeeld moeten we VERT.ZOEKEN gebruiken in de geschatte overeenkomstmodus, omdat in de meeste gevallen nooit een exacte overeenkomst zal worden gevonden. De VERT.ZOEKEN-formule in D5 is geconfigureerd om een overeenkomst bij benadering uit te voeren door het laatste argument in te stellen op WAAR:

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

VERT.ZOEKEN scant waarden in kolom G voor de opzoekwaarde. Als er een exacte overeenkomst wordt gevonden, zal VERT.ZOEKEN deze gebruiken. Als dit niet het geval is, zal VERT.ZOEKEN “een stap terug doen” en overeenkomen met de vorige rij.

Opmerking: gegevens moeten in oplopende volgorde worden gesorteerd op opzoekwaarde wanneer u de geschatte overeenkomstmodus gebruikt met VERT.ZOEKEN.

Eerste overeenkomst

In het geval van dubbele waarden, zal VERT.ZOEKEN de eerste overeenkomst vinden als de overeenkomstmodus exact is. In het onderstaande scherm is VERT.ZOEKEN geconfigureerd om de prijs voor de kleur “Groen” te vinden. Er zijn drie vermeldingen met de kleur Groen en VERT.ZOEKEN retourneert de prijs voor de eerste invoer, $ 17. De formule in cel F5 is:

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

Jokertekenovereenkomst

De functie VERT.ZOEKEN ondersteunt jokertekens, wat het mogelijk maakt om een gedeeltelijke overeenkomst uit te voeren op een opzoekwaarde. U kunt bijvoorbeeld VERT.ZOEKEN gebruiken om waarden uit een tabel op te halen nadat u slechts een deel van een opzoekwaarde hebt ingevoerd. Om jokertekens te gebruiken met VERT.ZOEKEN, moet u de exacte overeenkomstmodus specificeren door FALSE of 0 op te geven voor het laatste argument, range_lookup. De formule in H7 haalt de voornaam “Michael” op na het typen van “Aya” in cel H4:

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

Lees hier een meer gedetailleerde uitleg.

Tweeweg opzoeken

Binnen de functie VERT.ZOEKEN, de kolom indexargument is normaal gesproken hard gecodeerd als een statisch getal.U kunt echter ook een dynamische kolomindex maken door de functie MATCH te gebruiken om de rechterkolom te zoeken. Met deze techniek kunt u een dynamische zoekactie in twee richtingen maken, waarbij zowel rijen als kolommen overeenkomen. In het onderstaande scherm is VERT.ZOEKEN geconfigureerd om een zoekopdracht uit te voeren op basis van naam en maand. De formule in H6 is:

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

Zie dit voorbeeld voor meer details.

Opmerking: in het algemeen zijn INDEX en MATCH een meer flexibele manier om tweezijdige zoekopdrachten uit te voeren.

Meerdere criteria

De functie VERT.ZOEKEN verwerkt niet native meerdere criteria. U kunt echter een hulpkolom gebruiken om meerdere velden samen te voegen, en deze velden als meerdere criteria binnen VERT.ZOEKEN gebruiken. In het onderstaande voorbeeld is kolom B een hulpkolom die voor- en achternaam samenvoegt met deze formule:

=C5&D5 // helper column

VERT.ZOEKEN is geconfigureerd om hetzelfde te doen om een opzoekwaarde te creëren. De formule in H6 is:

=VLOOKUP(H4&H5,B5:E13,4,0)

Zie dit voorbeeld voor details.

Opmerking: INDEX en MATCH en XLOOKUP zijn robuustere manieren om zoekopdrachten af te handelen op basis van meerdere criteria.

VERT.ZOEKEN en # N / A-fouten

Als u VERT.ZOEKEN gebruikt, zult u onvermijdelijk de fout # N / A tegenkomen. De # N / A-fout betekent gewoon “niet gevonden”. In het onderstaande scherm bestaat de opzoekwaarde “Toy Story 2” bijvoorbeeld niet in de opzoektabel en retourneren alle drie de VERT.ZOEKEN-formules # N / A:

Een manier om de NA-fout te “vangen” is door de IFNA-functie als volgt te gebruiken:

De formule in H6 is:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

Het bericht kan naar wens worden aangepast. Om niets te retourneren (dwz om een blanco resultaat weer te geven) wanneer VERT.ZOEKEN # N / B retourneert, kunt u een lege tekenreeks als volgt gebruiken:

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

De # N / A-fout is handig omdat het aangeeft dat er iets mis is. In de praktijk zijn er veel redenen waarom u deze fout zou kunnen zien, waaronder:

De opzoekwaarde bestaat niet in de tabel
De opzoekwaarde is verkeerd gespeld of bevat extra ruimte
Overeenkomstmodus is exact, maar moet bij benadering zijn
Het tabelbereik is niet correct ingevoerd
U kopieert VERT.ZOEKEN en de tabelverwijzing is niet vergrendeld

Lees meer: VERT.ZOEKEN zonder # N / A-fouten

Meer over VERT.ZOEKEN

Meer VLOOKUP-voorbeelden
VLOOKUP-videos
23 tips voor het gebruik van VERT.ZOEKEN

Overige opmerkingen

Range_lookup bepaalt of de waarde exact moet overeenkomen of niet. De standaardwaarde is TRUE = niet-exacte overeenkomst toestaan.
Stel range_lookup in op FALSE om een exacte overeenkomst te vereisen en TRUE om een niet-exacte overeenkomst toe te staan.
Als range_lookup WAAR is (de standaardinstelling), zorgt een niet-exacte overeenkomst ervoor dat de functie VERT.ZOEKEN overeenkomt met de dichtstbijzijnde waarde in de tabel die nog steeds kleiner is dan de waarde.
Als bereik_opzoeken wordt weggelaten, staat de functie VERT.ZOEKEN een niet-exacte match, maar het zal een exacte match gebruiken als die bestaat.
Als range_lookup TRUE is (de standaardinstelling), zorg er dan voor dat de opzoekwaarden in de eerste rij van de tabel in oplopende volgorde worden gesorteerd. Anders kan VERT.ZOEKEN een onjuiste of onverwachte waarde retourneren.
Als range_lookup FALSE is (exacte overeenkomst vereist), hoeven waarden in de eerste kolom van de tabel niet te worden gesorteerd.

Excel VERT.ZOEKEN-functie