Hoe de Excel XLOOKUP-functie - te gebruiken

Samenvatting

De Excel XLOOKUP-functie is een moderne en flexibele vervanging voor oudere functies zoals VERT.ZOEKEN, HORIZ.ZOEKEN en ZOEKEN. XLOOKUP ondersteunt benaderende en exacte overeenkomsten, jokertekens (*?) Voor gedeeltelijke overeenkomsten en opzoekingen in verticale of horizontale bereiken.

Doel

Zoek waarden op in bereik of matrix

Winstwaarde

Overeenkomende waarde (n) uit retourmatrix

Syntaxis

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

Argumenten

  • lookup - De opzoekwaarde.
  • lookup_array - De array of het bereik om te zoeken.
  • return_array - De array of het bereik dat moet worden geretourneerd.
  • not_found - (optioneel) Waarde die moet worden geretourneerd als er geen overeenkomst is gevonden.
  • match_mode - (optioneel) 0 = exacte overeenkomst (standaard), -1 = exacte overeenkomst of volgende kleinste, 1 = exacte overeenkomst of volgende grotere, 2 = wildcard overeenkomst.
  • search_mode - (optioneel) 1 = zoeken vanaf eerste (standaard), -1 = zoeken vanaf laatste, 2 = binair zoeken oplopend, -2 = binair zoeken aflopend.

Versie

Excel 365

Gebruiksopmerkingen

XLOOKUP is een moderne vervanging voor de functie VERT.ZOEKEN. Het is een flexibele en veelzijdige functie die in veel verschillende situaties kan worden gebruikt.

XLOOKUP kan waarden in verticale of horizontale bereiken vinden, kan geschatte en exacte overeenkomsten uitvoeren en ondersteunt jokertekens (*?) Voor gedeeltelijke overeenkomsten. Bovendien kan XLOOKUP gegevens zoeken vanaf de eerste waarde of de laatste waarde (zie de details van het zoektype en de zoekmodus hieronder). Vergeleken met oudere functies zoals VERT.ZOEKEN, HORIZ.ZOEKEN en ZOEKEN, biedt XLOOKUP verschillende belangrijke voordelen.

Bericht niet gevonden

Wanneer XLOOKUP een overeenkomst niet kan vinden, retourneert het de fout # N / A, net als andere matchfuncties in Excel. In tegenstelling tot de andere matchfuncties ondersteunt XLOOKUP een optioneel argument met de naam not_found dat kan worden gebruikt om de # N / A-fout te negeren wanneer deze anders zou verschijnen. Typische waarden voor niet_ gevonden kunnen zijn "Niet gevonden", "Geen overeenkomst", "Geen resultaat", enz. Wanneer u een waarde opgeeft voor niet_ gevonden, plaats de tekst dan tussen dubbele aanhalingstekens ("").

Opmerking: Wees voorzichtig als u een lege string ("") opgeeft voor not_found. Als er geen overeenkomst wordt gevonden, geeft XLOOKUP niets weer in plaats van # N / A. Als u de # N / A-fout wilt zien wanneer er geen overeenkomst wordt gevonden, laat dan het argument volledig weg.

Overeenkomsttype

XLOOKUP voert standaard een exacte overeenkomst uit. Matchgedrag wordt bepaald door een optioneel argument genaamd match_type, dat de volgende opties heeft:

Overeenkomsttype Gedrag
0 (standaard) Exacte overeenkomst. Zal # N / A retourneren als er geen overeenkomst is.
-1 Exacte overeenkomst of volgend kleiner item.
1 Exacte overeenkomst of volgend groter item.
2 Jokertekenovereenkomst (*,?, ~)

Zoekmodus

XLOOKUP begint standaard met matchen vanaf de eerste gegevenswaarde. Zoekgedrag wordt bepaald door een optioneel argument genaamd zoekmodus , dat de volgende opties biedt:

Zoekmodus Gedrag
1 (standaard) Zoek vanaf de eerste waarde
-1 Zoeken vanaf laatste waarde (omgekeerd)
2 Binaire zoekwaarden gesorteerd in oplopende volgorde
-2 Binaire zoekwaarden gesorteerd in aflopende volgorde

Binaire zoekopdrachten zijn erg snel, maar de gegevens moeten naar behoefte worden gesorteerd. Als de gegevens niet correct zijn gesorteerd, kan een binaire zoekopdracht ongeldige resultaten opleveren die er volkomen normaal uitzien.

Voorbeeld # 1 - basis exacte overeenkomst

XLOOKUP voert standaard een exacte overeenkomst uit. In het onderstaande voorbeeld wordt XLOOKUP gebruikt om verkopen op te halen op basis van een exacte match op film. De formule in H5 is:

=XLOOKUP(H4,B5:B9,E5:E9)

Meer gedetailleerde uitleg hier.

Voorbeeld # 2 - basisovereenkomst bij benadering

Om een ​​geschatte overeenkomst mogelijk te maken, geeft u een waarde op voor het argument "match_mode". In het onderstaande voorbeeld wordt XLOOKUP gebruikt om een ​​korting te berekenen op basis van de hoeveelheid, waarvoor een geschatte overeenkomst vereist is. De formule in F5 levert -1 voor match_mode om geschatte overeenkomst met "exacte overeenkomst of het volgende kleinste" gedrag mogelijk te maken:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Meer gedetailleerde uitleg hier.

Voorbeeld # 3 - meerdere waarden

XLOOKUP kan meer dan één waarde tegelijkertijd retourneren voor dezelfde overeenkomst. Het onderstaande voorbeeld laat zien hoe XLOOKUP kan worden geconfigureerd om drie overeenkomende waarden te retourneren met een enkele formule. De formule in C5 is:

=XLOOKUP(B5,B8:B15,C8:E15)

Merk op dat de return-array (C8: E15) 3 kolommen bevat: First, Last, Department. Alle drie de waarden worden geretourneerd en lopen over in het bereik C5: E5.

Voorbeeld # 4 - zoeken in twee richtingen

XLOOKUP kan worden gebruikt om een ​​zoekactie in twee richtingen uit te voeren door een XLOOKUP in een andere te nesten. In het onderstaande voorbeeld haalt de "innerlijke" XLOOKUP een hele rij op (alle waarden voor Glass), die wordt overgedragen aan de "buitenste" XLOOKUP als de retourarray. De buitenste XLOOKUP zoekt de juiste groep (B) en retourneert de bijbehorende waarde (17.25) als het eindresultaat.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Meer details hier.

Voorbeeld # 5 - bericht niet gevonden

Net als andere opzoekfuncties, als XLOOKUP geen waarde vindt, retourneert het de fout # N / B. Om een ​​aangepast bericht weer te geven in plaats van # N / A, geeft u een waarde op voor het optionele "niet gevonden" argument, tussen dubbele aanhalingstekens (""). Om bijvoorbeeld 'Niet gevonden' weer te geven wanneer er geen overeenkomende film is gevonden, gebruikt u op basis van het onderstaande werkblad:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

U kunt dit bericht naar wens aanpassen: "Geen overeenkomst", "Film niet gevonden", enz.

Voorbeeld # 6 - complexe criteria

Met de mogelijkheid om arrays native af te handelen, kan XLOOKUP worden gebruikt met complexe criteria. In het onderstaande voorbeeld komt XLOOKUP overeen met het eerste record waarbij: account begint met "x" en regio "oost" is en maand niet april:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Details: (1) eenvoudig voorbeeld, (2) complexer voorbeeld.

XLOOKUP voordelen

XLOOKUP biedt verschillende belangrijke voordelen, vooral in vergelijking met VERT.ZOEKEN:

  • XLOOKUP kan gegevens rechts of links van opzoekwaarden opzoeken
  • XLOOKUP kan meerdere resultaten retourneren (voorbeeld # 3 hierboven)
  • XLOOKUP is standaard ingesteld op een exacte overeenkomst (VLOOKUP is standaard bij benadering)
  • XLOOKUP kan werken met verticale en horizontale gegevens
  • XLOOKUP kan een omgekeerde zoekopdracht uitvoeren (laatste naar eerste)
  • XLOOKUP kan hele rijen of kolommen retourneren, niet slechts één waarde
  • XLOOKUP kan native met arrays werken om complexe criteria toe te passen

Opmerkingen

  1. XLOOKUP kan werken met zowel verticale als horizontale arrays.
  2. XLOOKUP retourneert # N / A als de opzoekwaarde niet wordt gevonden.
  3. De lookup_array moet een dimensie hebben die compatibel is met het return_array- argument, anders retourneert XLOOKUP #VALUE!
  4. Als XLOOKUP wordt gebruikt tussen werkmappen, moeten beide werkmappen geopend zijn, anders retourneert XLOOKUP #REF !.
  5. Net als de functie INDEX retourneert XLOOKUP een verwijzing als resultaat.

Gerelateerde video's

Eenvoudig XLOOKUP-voorbeeld In deze video zullen we de XLOOKUP-functie opzetten met een eenvoudig voorbeeld. Passend op de naam van de stad, zullen we land en bevolking ophalen. Basic XLOOKUP geschatte match In deze video zullen we de XLOOKUP-functie instellen om een ​​geschatte match uit te voeren om een ​​op hoeveelheid gebaseerde korting te berekenen. XLOOKUP met booleaanse logica In deze video zullen we kijken hoe je de XLOOKUP-functie met Booleaanse logica kunt gebruiken om meerdere criteria toe te passen. XLOOKUP met meerdere opzoekwaarden In deze video zullen we XLOOKUP instellen om meerdere waarden in een dynamische array te retourneren, door een reeks opzoekwaarden op te geven in plaats van een enkele opzoekwaarde.

Interessante artikelen...