De nieuwe XLOOKUP-functie wordt vanaf november 2019 uitgerold naar Office 365. Joe McDaid van het Excel-team heeft XLOOKUP ontworpen om de mensen die VERT.ZOEKEN gebruiken en de mensen die INDEX / MATCH gebruiken te verenigen. In dit gedeelte worden de 12 voordelen van XLOOKUP besproken:
- Exacte overeenkomst is de standaard.
- Het op geheel getal gebaseerde derde argument van VERT.ZOEKEN is nu een juiste referentie.
- IFNA is ingebouwd om ontbrekende waarden te verwerken.
- XLOOKUP heeft geen probleem om naar links te gaan.
- Zoek de volgende kleinere of volgende grotere overeenkomst zonder de tabel te sorteren.
- XLOOKUP kan HLOOKUP doen.
- Vind de laatste overeenkomst door onderaan te zoeken.
- Jokertekens zijn standaard "uit", maar u kunt ze weer inschakelen.
- Retourneer alle 12 maanden in één formule.
- Kan een celverwijzing retourneren als XLOOKUP naast een dubbele punt staat, zoals XLOOKUP (); XLOOKUP ()
- Kan een tweeweg match doen zoals INDEX (, MATCH, MATCH) kan doen.
- Kan alle opzoekopdrachten samenvatten in een enkele formule, zoals LOOKUP zou kunnen doen.
Hier is de syntaxis: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).
XLOOKUP Voordeel 1: standaard exacte overeenkomst
99% van mijn VLOOKUP-formules eindigen op, FALSE of, 0 om een exacte overeenkomst aan te geven. Als je altijd de exacte matchversie van VERT.ZOEKEN gebruikt, kun je de match_mode uit je XLOOKUP-functie laten staan.
In de volgende afbeelding zoekt u W25-6 op vanuit cel A4. U wilt dat item zoeken in L8: L35. Als het gevonden is, wil je de corresponderende prijs uit kolom N. Het is niet nodig om False op te geven als match_mode omdat XLOOKUP standaard een exacte match is.
XLOOKUP Voordeel 2: de Results_Array is een verwijzing in plaats van een geheel getal
Denk aan de VERT.ZOEKEN-formule die u vóór XLOOKUP zou gebruiken. Het derde argument zou een 3 zijn geweest om aan te geven dat je de derde kolom wilde retourneren. Het gevaar bestond altijd dat een onwetende collega een kolom in uw tabel zou hebben ingevoegd (of verwijderd). Met een extra kolom in de tabel zou de VERT.ZOEKEN die een prijs had geretourneerd, een beschrijving gaan retourneren. Omdat XLOOKUP naar een celverwijzing wees, herschrijft de formule zichzelf om te blijven wijzen naar de prijs die nu in kolom O staat.
XLOOKUP Voordeel 3: IFNA is ingebouwd als een optioneel argument
De gevreesde # N / A-fout wordt geretourneerd wanneer uw opzoekwaarde niet in de tabel wordt gevonden. Om # N / A te vervangen door iets anders, moest u in het verleden IFERROR of IFNA gebruiken die om het VERT.ZOEKEN is gewikkeld.
Dankzij een suggestie van Rico op mijn YouTube-kanaal heeft het Excel-team een optioneel vierde argument toegevoegd voor if_not_found. Als u die # N / A-fouten door nul wilt vervangen, voegt u gewoon 0 toe als het vierde argument. Of u kunt wat tekst gebruiken, zoals "Waarde niet gevonden".
XLOOKUP Voordeel 4: Geen probleem om naar links van het sleutelveld te kijken
VERT.ZOEKEN kan niet naar links van het sleutelveld kijken zonder toevlucht te nemen tot VERT.ZOEKEN (A4, CHOOSE ((1,2), G7: G34, F7: F34), 2, False). Met XLOOKUP is het geen probleem om de Results_array links van de Lookup_array te hebben.
XLOOKUP Voordeel 5: Volgende kleinere of volgende grotere overeenkomst zonder te sorteren
VERT.ZOEKEN had een optie om te zoeken naar de exacte overeenkomst of alleen een kleinere waarde. U kunt het vierde argument weglaten uit VERT.ZOEKEN, of de waarde False wijzigen in Waar. Om dit te laten werken, moest de opzoektabel in oplopende volgorde worden gesorteerd.
Maar VERT.ZOEKEN had niet de mogelijkheid om de exacte overeenkomst of het volgende grotere item te retourneren. Daarvoor moest je overschakelen naar het gebruik van MATCH met een -1 als match_mode en moest je oppassen dat de opzoektabel aflopend werd gesorteerd.
XLOOKUP's optionele vijfde argument match_mode kan alleen zoeken naar de exacte overeenkomst, gelijk aan of net kleiner, gelijk aan of net groter. Merk op dat de waarden in XLOOKUP logischer zijn dan in MATCH:
- -1 vindt de waarde gelijk aan of net kleiner
- 0 vind een exacte match
- 1 vindt de waarde gelijk aan of net groter.
Maar het meest verbazingwekkende deel: de opzoektabel hoeft niet te worden gesorteerd en elke match_mode zal werken.
Hieronder vind je met een match_mode van -1 het volgende kleinere item.
Hier vindt een match_mode van 1, welk voertuig nodig is, afhankelijk van het aantal mensen in het gezelschap. Houd er rekening mee dat de opzoektabel niet op passagiers is gesorteerd en dat de voertuignaam links van de sleutel staat.
De tafel zegt:
- Bus biedt plaats aan 64 mensen
- Auto biedt plaats aan 4 personen
- Motor biedt plaats aan 1 persoon
- Tour Van biedt plaats aan 12 personen
- Van biedt plaats aan 6 personen.
Als bonus worden de gegevens gesorteerd op voertuig (in de oude oplossing, met MATCH, zou de tabel aflopend moeten worden gesorteerd op capaciteit. Ook: het voertuig staat links van capaciteit.
XLOOKUP Voordeel 6: Sideways XLOOKUP vervangt HLOOKUP
De lookup_array en results_array kunnen horizontaal zijn met XLOOKUP, waardoor het eenvoudig is om HLOOKUP te vervangen.
XLOOKUP Voordeel 7: zoek van onderaf naar de laatste overeenkomst
Ik heb een oude video op YouTube waarin een vraag van een Britse paardenboerderij wordt beantwoord. Ze hadden een wagenpark. Elke keer dat een voertuig binnenkwam voor brandstof of service, registreerden ze voertuig, datum en kilometerstand in een spreadsheet. Ze wilden voor elk voertuig de laatst bekende kilometerstand vinden. Hoewel MAXIFS in het Excel-2017-tijdperk dit vandaag zou kunnen oplossen, was de oplossing vele jaren geleden een geheimzinnige formule met LOOKUP en een deling door nul.
Tegenwoordig kunt u met het optionele zesde argument van XLOOKUP specificeren dat de zoekactie onderaan de dataset moet beginnen.
Opmerking
Hoewel dit een grote verbetering is, kun je alleen de eerste of laatste overeenkomst vinden. Sommige mensen hoopten dat je hierdoor de tweede of derde overeenkomst zou kunnen vinden, maar dat is niet de bedoeling van het argument search_mode.
Voorzichtigheid
De afbeelding hierboven laat zien dat er zoekmodi zijn die de oude binaire zoekfunctie gebruiken. Joe McDaid raadt het gebruik hiervan af. Ten eerste is het verbeterde zoekalgoritme uit 2018 snel genoeg dat er geen significant snelheidsvoordeel is. Ten tweede loop je het risico dat een onwetende collega de opzoektabel sorteert en foute antwoorden introduceert.
XLOOKUP Voordeel 8: Wildcards zijn standaard "uitgeschakeld"
De meeste mensen wisten niet dat VERT.ZOEKEN asterisk, vraagteken en tilde behandelt als jokertekens, zoals beschreven in "# 51 Gebruik een jokerteken in VERT.ZOEKEN" op pagina 143. Met XLOOKUP zijn jokertekens standaard uitgeschakeld. Als je wilt dat XLOOKUP deze tekens behandelt als een jokerteken, gebruik dan 2 als de Match_Mode.
XLOOKUP Voordeel 9: retourneer alle 12 maanden in één formule!
Dit is echt een voordeel van Dynamic Arrays, maar het is mijn favoriete reden om van XLOOKUP te houden. Wanneer u alle 12 maanden in een zoekactie moet retourneren, levert een enkele formule die is ingevoerd in B6 met een rechthoekige return_array meerdere resultaten op. Die resultaten zullen in aangrenzende cellen terechtkomen.
In de onderstaande afbeelding retourneert een enkele formule die is ingevoerd in B7 alle 12 antwoorden die worden weergegeven in B7: M7.
XLOOKUP Voordeel 10: kan een celverwijzing retourneren als deze grenst aan een dubbele punt
Deze is complex maar mooi. In het verleden waren er zeven functies die zouden veranderen van het retourneren van een celwaarde naar het retourneren van een celverwijzing als de functie een dubbele punt aanraakte. Zie A2: INDEX () gebruiken als een niet-vluchtige OFFSET voor een voorbeeld. XLOOKUP is de achtenfunctie om dit gedrag aan te bieden, lid van CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET en SWITCH.
Beschouw de volgende figuur. Iemand selecteert Cherry in E4 en Fig in E5. U wilt een formule die alles van B6 tot B9 optelt.
In de bovenstaande afbeelding kun je zien dat een XLOOKUP van E4 de 15 uit cel B6 retourneert. Een XLOOKUP van E5 retourneert de 30 van B9. Als u echter de twee XLOOKUP-functies uit de cellen D9 en D10 neemt en ze samenvoegt met een dubbele punt ertussen, verandert het gedrag van XLOOKUP. In plaats van 15 te retourneren, retourneert de eerste XLOOKUP het celadres B6!
Om dit te bewijzen, heb ik D7 geselecteerd en Formules, Evaluate Formula gebruikt. Nadat u tweemaal op Evalueren heeft gedrukt, is het volgende te berekenen deel XLOOKUP ("Cherry", A4: A29, B4: B29), zoals hier wordt weergegeven.
Druk nogmaals op Evalueren en verbazingwekkend genoeg retourneert de XLOOKUP-formule $ B $ 6 in plaats van de 15 opgeslagen in B6. Dit gebeurt omdat er onmiddellijk een dubbele punt volgt op deze XLOOKUP-formule.
Druk nog twee keer op Evalueren en de tussenformule is = SOM (B6: B9).
Dit is verbazingwekkend gedrag waar de meeste mensen niets vanaf weten. Excel MVP Charles Williams vertelt me dat het kan worden geactiveerd met een van deze drie operators naast XLOOKUP:
- Dikke darm
- Spatie (operator voor kruispunt)
- Komma (Union-operator)
XLOOKUP voordeel 11: tweeweg match zoals INDEX (, MATCH, MATCH)
Voor al mijn VLOOKUP-vrienden hebben de INDEX / MATCH-mensen gewacht om te zien of XLOOKUP een tweeweg match aankan. Het goede nieuws: het kan het. Het slechte nieuws: de methodologie is een beetje anders dan de INDEX / MATCH-fans zouden verwachten. Het is misschien een beetje boven hun hoofd. Maar ik weet zeker dat ze op deze methode kunnen terugkomen.
Voor een tweezijdige match wilt u weten welke rij het rekeningnummer A621 bevat dat wordt weergegeven in J3. De XLOOKUP begint dus eenvoudig genoeg: = XLOOKUP (J3, A5: A15. Maar dan moet je een results_array opgeven. Je kunt dezelfde truc gebruiken als in XLOOKUP Voordeel 9: Alle 12 maanden retourneren in een enkele formule hierboven, maar gebruik het om een verticale vector te retourneren. Een innerlijke XLOOKUP zoekt naar de J4-maand in de maandkoppen in B4: G4. De return_array wordt gespecificeerd als B5: G15. Het resultaat is dat de binnenste XLOOKUP een array retourneert zoals weergegeven in I10 : I20 hieronder. Omdat A621 wordt gevonden in de vijfde cel van de lookup_array en 104 wordt gevonden in de vijfde cel van de results_array, krijg je het juiste antwoord uit de formule. Hieronder toont J6 de oude manier. J7 geeft de nieuwe manier terug.
XLOOKUP voordeel 12: som alle opzoekwaarden op in één formule
De oude ZOEKEN-functie bood twee vreemde trucs. Ten eerste, als u probeert het totale bedrag aan bonusuitgaven te berekenen, kunt u LOOKUP vragen om alle waarden in één formule op te zoeken. In de afbeelding hieronder, LOOKUP (C4: C14 doet 11 lookups. Maar de LOOKUP-functie bood geen exacte overeenkomst en vereiste dat de opzoektabel werd gesorteerd.
Met XLOOKUP kunt u een bereik specificeren als de lookup_value en XLOOKUP zal alle antwoorden retourneren. Het voordeel is dat XLOOKUP exacte overeenkomsten kan doen.
Bonustip: hoe zit het met een gedraaide LOOKUP?
Excel MVP Mike Girvin laat vaak een truc van de LOOKUP-functie zien waarbij de Lookup_Vector verticaal is en de Result_Vector horizontaal. XLOOKUP ondersteunt deze truc niet standaard. Maar als je een beetje vals speelt en de results_array in de functie TRANSPONEREN wikkelt, kun je een verwrongen lookup beheren.
