1. Software Microsoft OfficeExcel De XLOOKUP-functie gebruiken in Excel 2016

Van Greg Harvey

Excel 2016 voor Office 365-abonnees op Windows en Mac ondersteunt nu een nieuwe XLOOKUP-functie, aangeprezen als een aanzienlijk eenvoudiger en veelzijdiger vervanging voor de zeer populaire (maar vaak kwaadaardige) verticale opzoekfunctie, VLOOKUP (weet niet wat de X in XLOOKUP staat voor; eXtensive, misschien?).

Voor degenen onder u die nog niet bekend zijn met VLOOKUP (beschouwd als de derde meest gebruikte functie direct na SOM en GEMIDDELDE), zoekt deze functie verticaal per rij in de meest linkse kolom van een aangewezen opzoektabel van boven naar beneden totdat het een waarde vindt in een opzoekkolom aangeduid door een offsetnummer dat overeenkomt met of groter is dan het getal dat u opzoekt. Hoewel enorm handig voor het lokaliseren van bepaalde items in een lange lijst of kolom van een gegevenstabel in uw werkblad, heeft de VLOOKUP-functie verschillende beperkingen die niet worden gedeeld door deze nieuwe opzoekfunctie, als XLOOKUP:

  • Standaard wordt het vinden van exacte overeenkomsten voor uw opzoekwaarde in het opzoekbereik gevonden Kan zowel verticaal (per rij) als horizontaal (per kolom) in een tabel zoeken, waardoor de noodzaak om de HLOOKUP-functie te gebruiken bij het horizontaal zoeken per kolom wordt vervangen Kan naar links of naar rechts zoeken, zodat het opzoekbereik in uw opzoektabel niet in een kolom links van de kolom die als retourbereik is aangegeven, hoeft te staan ​​om de functie te laten werken Wanneer de exacte standaardinstelling voor overeenkomsten wordt gebruikt, werkt deze zelfs wanneer waarden in het opzoekbereik niet in een bepaalde volgorde zijn gesorteerd Kan met behulp van een optioneel argument voor de zoekmodus van de onderste rij naar boven zoeken in het bereik van de opzoekreeks

De functie XLOOKUP heeft vijf mogelijke argumenten, waarvan de eerste drie verplicht zijn en de laatste twee optioneel, met de volgende syntaxis:

XLOOKUP (zoekwaarde, opzoekmatrix, return_array [match_mode], [search_mode])

Het vereiste argument lookup_value geeft de waarde of het item aan waarnaar u zoekt. Het vereiste argument array look_up-array geeft het cellenbereik aan dat moet worden doorzocht voor deze opzoekwaarde en het argument return_array geeft het cellenbereik aan met de waarde die u wilt retourneren wanneer Excel een exacte overeenkomst vindt.

* Houd er rekening mee dat bij het aanwijzen van de argumenten lookup_array en return_array in uw XLOOKUP-functie beide reeksen even lang moeten zijn, anders retourneert Excel de foutwaarde #WAARDE! fout in uw formule. Dit is nog meer de reden voor u om bereiknamen of kolomnamen van een toegewezen gegevenstabel te gebruiken bij het definiëren van deze argumenten in plaats van ze aan te wijzen of hun celverwijzingen in te typen.

Het optionele argument match_mode kan een van de volgende vier waarden bevatten:

  • 0 voor een exacte overeenkomst (de standaardwaarde, hetzelfde als wanneer geen match_mode-argument is aangewezen) -1 voor exacte overeenkomst of volgende mindere waarde 1 voor exacte overeenkomst of volgende grotere waarde 2 voor gedeeltelijke overeenkomst met jokertekens die zijn samengevoegd met de celverwijzing in het argument lookup_value

Het optionele argument search_mode kan een van de volgende vier waarden bevatten:

  • 1 om van voor naar achter te zoeken, dat wil zeggen van boven naar beneden (de standaardwaarde, hetzelfde als wanneer er geen argument search_mode is aangewezen) -1 om als laatste te zoeken, dat wil zeggen van onder naar boven 2 voor een binaire zoekopdracht in oplopende volgorde -2 voor binair zoeken in aflopende volgorde

De beste manier om de kracht en veelzijdigheid van de nieuwe XLOOKUP-functie te begrijpen, is deze in actie te zien in een Excel-werkblad. In de volgende afbeelding heb ik een werkblad met een eenvoudige 2019-verkoopgegevenstabel gerangschikt per land. Om XLOOKUP te gebruiken om de totale omzet uit deze tabel in cel E4 te retourneren op basis van het land dat u invoert in cel D4 van het werkblad, gaat u als volgt te werk:

  1. Plaats de celcursor in cel E4 van het werkblad Klik op de optie Opzoeken en referentie op het tabblad Formules gevolgd door XLOOKUP onderaan het vervolgkeuzemenu om het dialoogvenster Functieargumenten te openen. Klik op cel D4 in het werkblad om de celverwijzing in te voeren in het tekstvak Lookup_value argument. Druk op Tab om het tekstvak Lookup_array argument te selecteren en klik vervolgens op cel A4 en houd Shift ingedrukt terwijl u op Ctrl-pijl omlaag drukt om A4: A8 te selecteren als het te zoeken bereik (omdat het bereik A3: B8 is gedefinieerd als een Excel-gegevenstabel, Tabel 1 [Land] verschijnt in het tekstvak in plaats van het bereik A4: A8). Druk op Tab om het tekstvak Return_array argument te selecteren, klik vervolgens op cel B4 en houd Shift ingedrukt terwijl u op Ctrl-pijl omlaag drukt om B4: B8 te selecteren als het bereik met de te retourneren waarden op basis van de resultaten van de zoekopdracht (die verschijnt als Tabel1 [Totale verkoop] ​​in het tekstvak).

Klik op OK om de XLOOKUP-formule in cel E4 in te voeren.

XLOOKUP formule maken

Excel voert de XLOOKUP-formule in cel E4 van het werkblad in en retourneert 4900 als resultaat omdat Costa Rica momenteel is ingevoerd in de opzoekcel D4 en zoals u kunt zien in de 2019-verkooptabel, is dit inderdaad de totale omzet die voor dit land is gemaakt.

Omdat XLOOKUP net zo goed van links naar rechts werkt als van links naar rechts, kunt u deze functie net zo goed gebruiken om het land uit deze verkooptabel te retourneren op basis van een bepaald verkoopcijfer. De volgende afbeelding laat zien hoe u dit doet. Deze keer maakt u de XLOOKUP-formule in cel D4 en geeft u de waarde in cel E4 (in dit geval 11.000) aan als argument lookup_value.

Bovendien voert u -1 in als het argument match_mode om de exacte standaardinstelling van de functie te overschrijven, zodat Excel het land met een exacte overeenkomst retourneert naar de verkoopwaarde die is ingevoerd in de opzoekcel E4 of die met de volgende lagere totale omzet (Mexico met In dit geval $ 10.000 omdat er geen land in deze tabel is met $ 11.000 aan totale omzet). Zonder een match_mode-argument voor deze formule aan te geven, zou Excel #NA als resultaat retourneren, omdat er geen exacte match met $ 11.000 in deze verkooptabel is.

XLOOKUP-formule in D4

Omdat de XLOOKUP-functie net zo comfortabel is om horizontaal per kolom te zoeken als verticaal per rij te zoeken, kunt u deze gebruiken om een ​​formule te maken die een tweerichtingszoekactie uitvoert (vervangend de noodzaak om een ​​formule te maken die de INDEX- en MATCH-functies combineert als in het verleden). De volgende afbeelding, met de 2019-productietabel voor onderdeelnummers, AB-100 tot en met AB-103 voor de maanden april tot en met december, laat u zien hoe dit wordt gedaan.

geneste XLOOKUP-functies

In cel B12 heb ik de volgende formule gemaakt:

= XLOOKUP (part_lookup, $ A $ 3: $ A $ 6, XLOOKUP (date_lookup, $ B $ 2: $ J $ 2, $ B $ 3: $ J $ 6))

Deze formule begint met het definiëren van een XLOOKUP-functie die verticaal per rij zoekt naar een exacte overeenkomst met het onderdeelitem dat is gemaakt in de cel met de naam part_lookup (in dit geval cel B10) in het celbereik $ A $ 3: $ A $ 6 van de productietabel . Merk echter op dat het argument return_array voor deze originele LOOKUP-functie zelf een tweede XLOOKUP-functie is.

Deze tweede, geneste XLOOKUP-functie doorzoekt het celbereik $ B $ 2: $ J $ 2 horizontaal per kolom voor een exacte overeenkomst met de datuminvoer in de cel met de naam date_lookup (cel B11, in dit geval). Het argument return_array voor deze tweede, geneste XLOOKUP-functie is $ B $ 3: $ J $ 6, het celbereik van alle productiewaarden in de tabel.

De manier waarop deze formule werkt is dat Excel eerst het resultaat van de tweede, geneste XLOOKUP-functie berekent door een horizontale zoekopdracht uit te voeren die in dit geval de matrix in celbereik D3: D6 van de kolom Jun-19 retourneert (met de waarden: 438, 153, 306 en 779) als resultaat. Dit resultaat wordt op zijn beurt het argument return_array voor de oorspronkelijke XLOOKUP-functie die een verticale zoekactie per rij uitvoert voor een exacte overeenkomst met het onderdeelnummer in cel B11 (genaamd part_lookup). Omdat in dit voorbeeld deze deelopzoekcel AB-102 bevat, retourneert de formule alleen de productiewaarde Jun-19, 306, van het resultaat van de tweede, volgende XLOOKUP-functie.

Daar heb je het! Een eerste blik op XLOOKUP, een krachtige, veelzijdige en redelijk eenvoudig te gebruiken nieuwe opzoekfunctie die niet alleen de opzoekingen met één waarde kan uitvoeren die worden uitgevoerd door de VLOOKUP- en HLOOKUP-functies, maar ook de opzoekingen in twee richtingen door het combineren van de INDEX en MATCH-functies ook.

* Helaas is de XLOOKUP-functie niet achterwaarts compatibel met eerdere versies van Microsoft Excel die alleen de VLOOKUP- en HLOOKUP-functies ondersteunen of compatibel met huidige versies die het nog niet hebben als een van hun opzoekfuncties, zoals Excel 2019 en Excel Online. Dit betekent dat als u een werkmap met XLOOKUP-formules deelt met collega's of klanten die een versie van Excel gebruiken die deze nieuwe opzoekfunctie niet bevat, al deze formules #NAME retourneren? foutwaarden wanneer ze het werkblad openen.