Hoe de XLOOKUP-functie in Excel 2016 te gebruiken?

Excel 2016 voor Office 365-abonnees op Windows en Mac ondersteunt nu een nieuwe XLOOKUP-functie, aangeprezen als een aanzienlijk eenvoudigere en veelzijdigere vervanging voor de zeer populaire (maar vaak verguisde) verticale lookup-functie, VERT.ZOEKEN (weet niet wat de X in XLOOKUP is) staat voor; eXtensive misschien?).

Voor degenen onder u die nog niet bekend zijn met VERT.ZOEKEN (beschouwd als de derde meest gebruikte functie direct na SOM en GEMIDDELDE), deze functie zoekt 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 met een offsetnummer dat overeenkomt met of groter is dan het nummer 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 VERT.ZOEKEN-functie verschillende beperkingen die niet worden gedeeld door deze nieuwe opzoekfunctie, zoals XLOOKUP:

• Standaardinstelling voor het vinden van exacte overeenkomsten voor uw opzoekwaarde in het opzoekbereik
• Kan zowel verticaal (op rij) als horizontaal (op kolom) in een tabel zoeken, waardoor het gebruik van de HORIZ.ZOEKEN-functie bij horizontaal zoeken op kolom overbodig wordt
• Kan links of rechts zoeken, zodat het opzoekbereik in uw opzoektabel niet in een kolom aan de linkerkant van de kolom hoeft te staan ​​die is aangewezen als het retourbereik om de functie te laten werken
• Wanneer de exacte match-standaard wordt gebruikt, werkt het zelfs wanneer waarden in het opzoekbereik niet in een bepaalde volgorde zijn gesorteerd
• Kan zoeken van de onderste rij naar de top in het opzoekarraybereik, met behulp van een optioneel zoekmodusargument

De functie XZOEKEN heeft vijf mogelijke argumenten, waarvan de eerste drie vereist zijn en de laatste twee optioneel, met behulp van 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 array- argument look_up geeft het celbereik aan waarin naar deze lookup-waarde moet worden gezocht, en het argument return_array geeft het celbereik aan dat de waarde bevat die u wilt retourneren wanneer Excel een exacte overeenkomst vindt.

* Houd er rekening mee dat wanneer u de argumenten lookup_array en return_array in uw XLOOKUP-functie aanwijst, beide bereiken even lang moeten zijn, anders retourneert Excel de #VALUE! fout in uw formule. Dit is des te meer de reden voor u om bereiknamen of kolomnamen van een aangewezen 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 standaard, hetzelfde als wanneer er geen match_mode- argument is aangewezen)
• -1 voor exacte overeenkomst of volgende lagere waarde
• 1 voor exacte overeenkomst of volgende hogere waarde
• 2 voor gedeeltelijke overeenkomst met jokertekens die zijn samengevoegd met celverwijzing in het argument lookup_value

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

• 1 om de eerste naar de laatste te zoeken, dat wil zeggen van boven naar beneden (de standaard, hetzelfde als wanneer er geen zoekmodus- argument is aangewezen)
• -1 om van laatste naar eerste 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 door deze in actie te zien in een Excel-werkblad. In de volgende afbeelding heb ik een werkblad met een eenvoudige verkoopgegevenstabel voor 2019, gerangschikt per land. Als u XZOEKEN wilt gebruiken om de totale verkoop uit deze tabel in cel E4 te retourneren op basis van het land dat u invoert in cel D4 van het werkblad, voert u deze stappen uit:

Plaats de celcursor in cel E4 van het werkblad

Klik op de optie Opzoeken en verwijzen op het tabblad Formules, gevolgd door XZOEKEN onder aan 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, klik vervolgens op cel A4 en houd Shift ingedrukt terwijl u op Ctrl-pijl omlaag drukt om A4:A8 te selecteren als het bereik om te zoeken (omdat het bereik A3:B8 is gedefinieerd als een Excel-gegevenstabel, Tabel1[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 de Ctrl-pijl omlaag drukt om B4:B8 te selecteren als het bereik met de waarden die moeten worden geretourneerd op basis van de resultaten van de zoekopdracht (die wordt weergegeven als Tabel1 [Totale verkoop] ​​in het tekstvak).

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

Een formule maken met XZOEKEN in cel E4 die verkopen retourneert op basis van het land dat is ingevoerd in cel D4.

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 verkooptabel van 2019, is dit inderdaad de totale omzet voor dit land.

Omdat XLOOKUP net zo goed van rechts naar links 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. In de volgende afbeelding ziet u hoe u dit doet. Deze keer maakt u de XLOOKUP-formule in cel D4 en geeft u de waarde op die is ingevoerd in cel E4 (11.000, in dit geval) als het argument lookup_value.

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

Een formule maken met XLOOKUP in cel D4 die het land retourneert op basis van de verkopen die zijn ingevoerd in cel E4

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

Een formule maken met geneste XZOEKEN-functies om het aantal geproduceerde eenheden voor een onderdeel in een bepaalde maand te retourneren

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 de onderdeelinvoer die is gemaakt in de cel met de naam part_lookup (cel B10, in dit geval) in het celbereik $A$3:$A$6 van de productietabel . Merk echter op dat het argument return_array voor deze oorspronkelijke LOOKUP-functie zelf een tweede XLOOKUP-functie is.

Deze tweede, geneste XZOEKEN-functie doorzoekt het celbereik $B$2:$J$2 horizontaal per kolom voor een exacte overeenkomst met de datuminvoer die is gemaakt 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 XZOEKEN-functie berekent door een horizontale zoekopdracht uit te voeren die, in dit geval, de matrix retourneert in celbereik D3: D6 van de kolom Jun-19 (met de waarden: 438, 153, 306 en 779) als resultaat. Dit resultaat wordt op zijn beurt het argument return_array voor de originele XZOEKEN-functie die een verticale zoekactie per rij uitvoert voor een exacte overeenkomst met de onderdeelnummerinvoer die is gemaakt in cel B11 (met de naam part_lookup). Omdat deze cel part_lookup in dit voorbeeld AB-102 bevat, retourneert de formule alleen de productiewaarde van juni 19, 306, uit het resultaat van de tweede, volgende functie XLOOKUP.

Daar heb je het! Een eerste blik op XLOOKUP, een krachtige, veelzijdige en redelijk gebruiksvriendelijke nieuwe lookup-functie die niet alleen de single-value lookups kan uitvoeren die worden uitgevoerd door de VLOOKUP- en HLOOKUP-functies, maar ook de two-way value-lookups die worden uitgevoerd door de combinatie 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 deze nog niet als een van hun opzoekfuncties bevatten, zoals Excel 2019 en Excel Online . Dit betekent dat als u een werkmap met XZOEKEN-formules deelt met collega's of klanten die een versie van Excel gebruiken die deze nieuwe opzoekfunctie niet bevat, al deze formules #NAAM? foutwaarden wanneer ze het werkblad openen.