XLOOKUP -funktionen är exklusiv för insiderprogrammet för office 365. LOOKUP -funktionen har många funktioner som övervinner många av svagheterna i VLOOKUP- och HLOOKUP -funktionen men tyvärr är den inte tillgänglig för oss för tillfället. Men oroa dig inte, vi kan skapa en XLOOKUP -funktion som fungerar exakt samma som den kommande XLOOKUP -funktionen MS Excel. Vi kommer att lägga till funktioner en efter en.
VBA -kod för XLOOKUP -funktionen
Nedanstående UDF -sökfunktion kommer att lösa många problem. Kopiera den eller ladda ner xl-tillägget nedanför filen nedan.
Funktion XLOOKUP (lk som variant, lCol som intervall, rCol som intervall) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Avsluta funktion
Förklaring:
Ovanstående kod är bara grundläggande INDEX-MATCH som används i VBA. Detta förenklar många saker som en ny användare står inför. If löser komplexiteten hos INDEX-MATCH-funktionen och använder endast tre argument. Du kan kopiera den i din excel-fil eller ladda ner .xlam-filen nedan och installera den som ett tillägg i excel. Om du inte vet hur du skapar och använder ett tillägg, klicka här, det hjälper dig.
XLOOKUP-tillägg
låt oss se hur det fungerar på Excel -kalkylblad.
Syntax för XLOOKUP
| = XLOOKUP (lookup_value, lookup_array, result_array) |
lookup_value: Detta är värdet som du vill söka i lookup_array.
lookup_array: Det är ett endimensionellt område där du vill söka i uppslagningsvärde.
result_array: Det är också ett endimensionellt område. Detta är intervallet från vilket du vill hämta värdet.
Låt oss se denna XLOOKUP -funktion i aktion.
XLOOKUP Exempel:
Här har jag en datatabell i excel. Låt oss utforska några funktioner med denna datatabell.
Funktionalitet 1. Exakt Sökning till vänster och höger om uppslagsvärdet.
Som vi vet kan Excel VLOOKUP -funktionen inte hämta värden från vänster om uppslagsvärdet. För det måste du använda den komplexa INDEX-MATCH-kombinationen. Men inte längre.
Förutsatt att vi behöver hämta all information som finns tillgänglig i tabellen med några rullnummer. I så fall måste du också hämta regionen, som är till vänster om kolumnen för nummer nummer.
Skriv denna formel, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Vi får resultatet norr för rulle nummer 112. Kopiera eller dra ner formeln i cellerna nedan för att fylla dem med respektive regioner.
Hur fungerar det?
Mekanismen är enkel. Denna funktion letar upp uppslagningsvärde i lookup_array och returnerar indexet för en första exakt matchning. Använd sedan det indexet för att hämta värdet från result_array. Denna funktion fungerar perfekt med namngivna intervall.
På samma sätt använder du denna formel för att hämta värdet från varje kolumn.
Funktionalitet 2. Exakt Horisontell Slå upp ovanför och under uppslagsvärdet.
XLOOKUP fungerar också som en exakt HLOOKUP -funktion. HLOOKUP -funktionen har samma begränsning som VLOOKUP har. Det kan inte hämta värde ovanför uppslagsvärdet. Men XLOOKUP fungerar inte bara som HLOOKUP, det övervinner också den svagheten. Låt oss se hur.
Hypotetiskt, om du vill jämföra två poster. Sökposten du redan har. Rekordet du vill jämföra med ligger ovanför uppslagningsområdet. Använd den här formeln i så fall.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
dra ner formeln, och du har hela posten för att jämföra rad.
Funktionalitet 3. Inget behov av kolumnnummer och standardmatchning.
När du använder VLOOKUP -funktionen måste du berätta det kolumnnummer som du vill hämta värdena från. För det måste du räkna kolumnerna eller använda några knep, ta hjälp av andra funktioner. Med denna UDF XLOOKUP behöver du inte göra det.
Om du använder VLOOKUP för att bara hämta något värde från en kolumn eller för att kontrollera om det finns ett värde i kolumnen, är detta den bästa lösningen enligt mig.
Funktionalitet 4. Ersätter INDEX-MATCH, VLOOKUP, HLOOKUP funktion
För enkla uppgifter ersätter vår XLOOKUP-funktion ovanstående funktioner.
Begränsningar av XLOOKUP:
När det gäller komplexa formler, som VLOOKUP med Dynamic Col Index där vi VLOOKUP identifierar uppslagningskolumnen med rubriker, misslyckas denna XLOOKUP.
En annan begränsning är att om du måste leta upp flera slumpmässiga kolumner eller rader från tabellen kommer denna funktion att vara värdelös eftersom du måste skriva denna formel om och om igen. Detta kan övervinnas genom att använda namngivna intervall.
För tillfället har vi inte lagt till den ungefärliga funktionaliteten, så självklart kan du inte få den ungefärliga matchningen. Vi kommer att lägga till det för tidigt.
Om XLOOKUP -funktionen inte hittar uppslagsvärdet returnerar det #VÄRDE -fel inte #N/A.
Så ja killar, så här använder du XLOOKUP för att hämta, söka och validera värden i excel -tabeller. Du kan använda den här användardefinierade funktionen för en problemfri sökning till vänster eller uppåt av uppslagsvärdet. Om du fortfarande har några tvivel eller några specifika krav relaterade till den här funktionen eller EXCEL 2010/2013/2016/2019/365 eller VBA -relaterad fråga, fråga det i kommentarfältet nedan. Du får säkert svar.
Skapa VBA -funktion för att returnera matris | För att returnera en matris från användardefinierad funktion måste vi deklarera den när vi heter UDF.
Matriser i Excel Formul | Lär dig vilka matriser som finns i excel.
Hur man skapar användardefinierad funktion via VBA | Lär dig hur du skapar användardefinierade funktioner i Excel
Använda en användardefinierad funktion (UDF) från en annan arbetsbok med VBA i Microsoft Excel | Använd den användardefinierade funktionen i en annan arbetsbok i Excel
Returnera felvärden från användardefinierade funktioner med VBA i Microsoft Excel | Lär dig hur du kan returnera felvärden från en användardefinierad funktion
Populära artiklar:
Dela Excel -blad i flera filer baserat på kolumn med VBA | Den här VBA -koden delar Excel -basen på unika värden i en specifik kolumn. Ladda ner arbetsfilen.
Stäng av varningsmeddelanden med VBA i Microsoft Excel 2016 | För att stänga av varningsmeddelanden som avbryter den körande VBA -koden använder vi applikationsklassen.
Lägg till och spara ny arbetsbok med VBA i Microsoft Excel 2016 | För att lägga till och spara arbetsböcker med VBA använder vi Workbooks -klassen. Workbooks.Add lägger till ny arbetsbok enkelt, dock …