Hur man använder Excel XLOOKUP-funktionen

Sammanfattning

Excel XLOOKUP-funktionen är en modern och flexibel ersättning för äldre funktioner som VLOOKUP, HLOOKUP och LOOKUP. XLOOKUP stöder ungefärlig och exakt matchning, jokertecken (*?) För partiella matchningar och sökningar i vertikala eller horisontella intervall.

Ändamål

Sökvärden i intervall eller array

Returvärde

Matchande värde från returmatris

Syntax

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

Argument

  • lookup - Sökningsvärdet.
  • lookup_array - Matrisen eller intervallet som ska sökas.
  • return_array - Matrisen eller intervallet som ska returneras.
  • not_found - (valfritt) Värde att returnera om ingen matchning hittades.
  • match_mode - (valfritt) 0 = exakt matchning (standard), -1 = exakt matchning eller nästa minsta, 1 = exakt matchning eller nästa större, 2 = jokerteckenmatchning.
  • search_mode - (valfritt) 1 = sök från första (standard), -1 = sök från senaste, 2 = binär sökning stigande, -2 = binär sökning fallande.

Version

Excel 365

Användningsanmärkningar

XLOOKUP är en modern ersättning för VLOOKUP-funktionen. Det är en flexibel och mångsidig funktion som kan användas i en mängd olika situationer.

XLOOKUP kan hitta värden i vertikala eller horisontella intervall, kan utföra ungefärliga och exakta matchningar och stöder jokertecken (*?) För partiella matchningar. Dessutom kan XLOOKUP söka i data med början från det första eller det sista värdet (se matchningstyp och information om sökläge nedan). Jämfört med äldre funktioner som VLOOKUP, HLOOKUP och LOOKUP erbjuder XLOOKUP flera viktiga fördelar.

Hittade inte meddelande

När XLOOKUP inte kan hitta en matchning returnerar den # N / A-felet, som andra matchningsfunktioner i Excel. Till skillnad från de andra matchningsfunktionerna stöder XLOOKUP ett valfritt argument som heter not_found som kan användas för att lägga till # N / A-felet när det annars skulle visas. Typiska värden för not_found kan vara "Inte hittat", "Ingen matchning", "Inget resultat", etc. När du anger ett värde för not_found, bifoga texten med dubbla citat ("").

Obs! Var försiktig om du anger en tom sträng ("") för not_found. Om ingen matchning hittas visar XLOOKUP inget i stället för # N / A. Om du vill se # N / A-felet när en matchning inte hittas, utelämna argumentet helt.

Matchningstyp

Som standard utför XLOOKUP en exakt matchning. Matchbeteende styrs av ett valfritt argument som heter match_type, som har följande alternativ:

Matchningstyp Beteende
0 (standard) Exakt matchning. Kommer tillbaka # N / A om ingen matchning.
-1 Exakt matchning eller nästa mindre artikel.
1 Exakt matchning eller nästa större artikel.
2 Jokerteckenmatchning (*,?, ~)

Sökningsläge

Som standard kommer XLOOKUP att matcha från det första datavärdet. Sökbeteende styrs av ett valfritt argument som heter search_mode , vilket ger följande alternativ:

Sökningsläge Beteende
1 (standard) Sök från första värdet
-1 Sök från senaste värde (omvänd)
2 Binära sökvärden sorterade i stigande ordning
-2 Binära sökvärden sorterade i fallande ordning

Binära sökningar är mycket snabba, men data måste sorteras efter behov. Om data inte sorteras ordentligt kan en binär sökning returnera ogiltiga resultat som ser helt normala ut.

Exempel 1 - grundläggande exakt matchning

Som standard utför XLOOKUP en exakt matchning. I exemplet nedan används XLOOKUP för att hämta försäljning baserat på en exakt matchning på film. Formeln i H5 är:

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

Mer detaljerad förklaring här.

Exempel # 2 - grundläggande ungefärlig matchning

För att aktivera en ungefärlig matchning, ange ett värde för argumentet "match_mode". I exemplet nedan används XLOOKUP för att beräkna en rabatt baserat på kvantitet, vilket kräver en ungefärlig matchning. Formeln i F5 levererar -1 för match_mode för att möjliggöra ungefärlig matchning med "exakt matchning eller nästa minsta" beteende:

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

Mer detaljerad förklaring här.

Exempel # 3 - flera värden

XLOOKUP kan returnera mer än ett värde samtidigt för samma matchning. Exemplet nedan visar hur XLOOKUP kan konfigureras för att returnera tre matchande värden med en enda formel. Formeln i C5 är:

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

Lägg märke till att returmatrisen (C8: E15) innehåller tre kolumner: Först, Sista, Avdelning. Alla tre värden returneras och sprider sig inom intervallet C5: E5.

Exempel # 4 - tvåvägssökning

XLOOKUP kan användas för att utföra en tvåvägssökning genom att häcka en XLOOKUP inuti en annan. I exemplet nedan hämtar den "inre" XLOOKUP en hel rad (alla värden för Glass), som överlämnas till den "yttre" XLOOKUP som returmatris. Den yttre XLOOKUPEN hittar rätt grupp (B) och returnerar motsvarande värde (17.25) som slutresultat.

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

Mer information här.

Exempel nr 5 - hittades inte meddelande

Liksom andra uppslagsfunktioner, om XLOOKUP inte hittar ett värde, returnerar det # N / A-felet. För att visa ett anpassat meddelande istället för # Ej tillämpligt, ange ett värde för det valfria argumentet "hittades inte", omges av dubbla citat (""). Om du till exempel vill visa "Hittades inte" när ingen matchande film hittades, baserat på kalkylbladet nedan, använd:

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

Du kan anpassa det här meddelandet som du vill: "Ingen matchning", "Film hittades inte", etc.

Exempel # 6 - komplexa kriterier

Med förmågan att hantera matriser inbyggt kan XLOOKUP användas med komplexa kriterier. I exemplet nedan matchar XLOOKUP den första posten där: kontot börjar med "x" och regionen är "öst" och månaden inte är april:

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

Detaljer: (1) enkelt exempel, (2) mer komplicerat exempel.

XLOOKUP-fördelar

XLOOKUP erbjuder flera viktiga fördelar, särskilt jämfört med VLOOKUP:

  • XLOOKUP kan slå upp data till höger eller vänster om uppslagsvärden
  • XLOOKUP kan returnera flera resultat (exempel # 3 ovan)
  • XLOOKUP är som standard en exakt matchning (VLOOKUP är som standard ungefärlig)
  • XLOOKUP kan arbeta med vertikala och horisontella data
  • XLOOKUP kan utföra en omvänd sökning (sista till första)
  • XLOOKUP kan returnera hela rader eller kolumner, inte bara ett värde
  • XLOOKUP kan arbeta med arrays för att tillämpa komplexa kriterier

Anteckningar

  1. XLOOKUP kan fungera med både vertikala och horisontella matriser.
  2. XLOOKUP returnerar # N / A om sökningsvärdet inte hittas.
  3. Den uppslagnings måste ha en dimension kompatibel med return_array argument, annars XLOOKUP kommer tillbaka #VALUE!
  4. Om XLOOKUP används mellan arbetsböcker måste båda arbetsböckerna vara öppna, annars returnerar XLOOKUP #REF !.
  5. Precis som INDEX-funktionen returnerar XLOOKUP en referens som ett resultat.

Relaterade videoklipp

Grundläggande XLOOKUP-exempel I den här videon ställer vi in ​​XLOOKUP-funktionen med ett grundläggande exempel. Om vi ​​matchar stadens namn hämtar vi land och befolkning. Grundläggande XLOOKUP-matchning I den här videon ställer vi in ​​XLOOKUP-funktionen för att utföra en ungefärlig matchning för att beräkna en kvantitetsbaserad rabatt. XLOOKUP med boolesk logik I den här videon tittar vi på hur man använder XLOOKUP-funktionen med boolesk logik för att tillämpa flera kriterier. XLOOKUP med flera uppslagsvärden I den här videon ställer vi in ​​XLOOKUP för att returnera flera värden i en dynamisk matris genom att tillhandahålla ett intervall med uppslagsvärden istället för ett enda uppslagsvärde.

Intressanta artiklar...