VLOOKUP versus XLOOKUP: Kies de juiste opzoekformule

Gebruik XLOOKUP voor de meeste nieuwe zoekopdrachten als uw werkmap wordt geopend in LibreOffice 24.8 of later; deze functie zoekt standaard naar een exacte overeenkomst en stelt u in staat om aparte zoek- en resultaatbereiken te kiezen. Gebruik VLOOKUP wanneer compatibiliteit met oudere Calc-versies nodig is of wanneer de zoekwaarde zich in de eerste kolom van een compacte tabel bevindt. Als u VLOOKUP blijft gebruiken, voeg dan 0 of FALSE toe aan het vierde argument voor een exacte overeenkomst.

Deze functies lossen in principe hetzelfde probleem op: een sleutel vinden, zoals een productcode, en gerelateerde informatie uit een andere cel ophalen. De belangrijkste verschillen zitten in de manier waarop elke formule zijn bereik definieert, wat er wordt aangenomen wanneer een optie wordt weggelaten, en of het bestand compatibel moet zijn met oudere software.

Wat is het praktische verschil?

VLOOKUP zoekt in de eerste kolom van een rechthoekige tabel en geeft een waarde terug uit een genummerde kolom rechts daarvan. XLOOKUP scheidt het zoekbereik van het retourbereik, waardoor de retourwaarden zich aan beide zijden van de sleutels kunnen bevinden. XLOOKUP accepteert ook een aangepaste waarde voor een ontbrekende overeenkomst en kan in omgekeerde volgorde zoeken.

Stel bijvoorbeeld dat A2:A5 product-ID's bevat en B2:B5 prijzen. Cel F2 bevat de ID waarnaar gezocht moet worden. In Calc is de VLOOKUP-formule voor exacte overeenkomst als volgt:

=VLOOKUP(F2;A2:B5;2;0)

De equivalente XLOOKUP-formule is:

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

Beide functies geven de overeenkomende prijs terug. Calc gebruikt doorgaans puntkomma's tussen functieargumenten, afhankelijk van de landinstellingen; als formules in uw installatie komma's gebruiken, gebruik dan het scheidingsteken dat Calc verwacht. De voorbeelden gebruiken resultaten van één cel en eendimensionale opzoekbereiken.

Wanneer moet je voor XLOOKUP kiezen?

Kies XLOOKUP voor een nieuwe werkmap als u weet dat de ontvangers LibreOffice 24.8 of nieuwer gebruiken. De functie is beschikbaar sinds LibreOffice 24.8, volgens de officiële XLOOKUP-helppagina . De standaard zoekmodus is exact, dus een ontbrekende exacte sleutel retourneert niet stilzwijgend een nabijgelegen waarde.

Het functiepatroon is =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]). De eerste drie argumenten identificeren de sleutel, het eendimensionale zoekbereik en het bereik waaruit moet worden geretourneerd. Een vierde argument, zoals , "Not found"vervangt het gebruikelijke #N/A-resultaat wanneer er geen overeenkomst wordt gevonden.

Geef een waarde terug uit een kolom aan de linkerkant.

VLOOKUP kan geen waarde retourneren die zich links van de eerste tabelkolom bevindt. XLOOKUP kan dat wel, omdat het zoek- en resultaatbereik onafhankelijk van elkaar zijn. Als de ID's bijvoorbeeld in C2:C100 staan ​​en de afdelingsnamen in A2:A100, gebruik dan:

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

Deze lay-out voorkomt ook een kwetsbare kolomindex. Als iemand een kolom in een VLOOKUP-tabel invoegt, kan de vastgelegde index naar een ander veld verwijzen. Het expliciet opgegeven resultaatbereik van XLOOKUP maakt het gemakkelijker om de beoogde retourkolom te controleren.

Retourneer de meest recente overeenkomende invoer.

Als er dubbele sleutels aanwezig zijn en u de laatste overeenkomst wilt, ondersteunt XLOOKUP omgekeerd zoeken met de zoekmodus. Voor een verticaal bereik -1zoekt het laatste argument van laatst naar eerst:

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

Dit retourneert het resultaat dat hoort bij de laatst exacte voorkomst. Gebruik dit alleen wanneer de rijvolgorde een gedefinieerde betekenis heeft, bijvoorbeeld dat de nieuwste records als laatste verschijnen. Als duplicaten als een fout moeten worden behandeld, moet u de gegevens opschonen of valideren in plaats van impliciet één voorkomst te kiezen.

Wanneer is VLOOKUP nog steeds de betere keuze?

Behoud VLOOKUP wanneer het bestand moet worden geopend in een oudere versie van LibreOffice die geen XLOOKUP bevat, of wanneer de doelapplicatie XLOOKUP mogelijk niet herkent. De officiële Calc-documentatie beschrijft de tabel- en indexargumenten van VLOOKUP en het optionele argument voor een gesorteerd bereik; raadpleeg de officiële referentie voor spreadsheetfuncties .

Voor een exacte overeenkomst is het patroon =VLOOKUP(key;table;column_index;0). De index telt de kolommen vanaf de linkerkant van de geselecteerde tabel: 1 betekent de eerste kolom, 2 de tweede. De zoekcode moet in die eerste kolom staan ​​en het geretourneerde veld moet zich rechts daarvan bevinden.

Het vierde argument is belangrijk. Als u dit weglaat of op TRUE instelt, gebruikt VLOOKUP een benaderende overeenkomst en verwacht dat de eerste kolom in oplopende volgorde is gesorteerd. Dit gedrag kan handig zijn voor bereiken zoals belastingtarieven, waarbij elke rij een ondergrens aangeeft. Het is echter riskant voor ID's, namen en ongesorteerde lijsten. Geef in die gevallen 0 of FALSE op.

Hoe verhouden het gedrag bij een overeenkomst en bij een foutmelding zich tot elkaar?

• Exacte overeenkomst: XLOOKUP gebruikt standaard een exacte overeenkomst. VLOOKUP vereist dat het vierde argument op 0 of FALSE wordt ingesteld voor een gegarandeerde exacte zoekopdracht.
• Ontbrekende sleutel: XLOOKUP kan een gekozen bericht of waarde retourneren in het vierde argument. VLOOKUP retourneert over het algemeen #N/A; gebruik IFNA als u een aangepast bericht nodig hebt.
• Bij benadering overeenkomen: Beide methoden ondersteunen bereikgebaseerde overeenkomsten, maar de gegevens en de geselecteerde modus moeten overeenkomen met de beoogde regel. Sorteer de gegevens indien nodig en controleer de grensgevallen.
• Zoekrichting: XLOOKUP kan van onder naar boven zoeken met het argument search-mode. VLOOKUP retourneert de eerste overeenkomende rij bij normaal gebruik van boven naar beneden.

Om een ​​aangepaste reactie te geven bij een ontbrekende sleutel met VLOOKUP, gebruikt u =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found"). Hierdoor blijft de instelling voor exacte overeenkomst zichtbaar en wordt alleen de foutmelding 'niet beschikbaar' afgehandeld. Vermijd het plaatsen van de formule in een algemene foutafhandeling, tenzij u ook andere problemen wilt verbergen, zoals een ongeldige bereikverwijzing.

Wat moet je controleren voordat je een werkmap deelt?

De helpfunctie van LibreOffice vermeldt dat XLOOKUP beschikbaar is sinds versie 24.8. Er wordt ook opgemerkt dat XLOOKUP geen onderdeel uitmaakt van de OpenFormula-standaard van OpenDocument versie 1.3, en er wordt een Microsoft-functienaamruimte voor interoperabiliteit genoemd. Dit betekent dat het bestandsformaat op zich geen garantie biedt dat elke oudere of spreadsheettoepassing van derden de functie kan berekenen. Test in gemengde softwareomgevingen het werkblad in de oudste doeltoepassing of gebruik een gevestigde formule zoals VLOOKUP met exacte overeenkomst, indien de lay-out daarvan geschikt is.

Let ook op een gedocumenteerde voorwaarde voor matrixformules: wanneer de resultaatmatrix van XLOOKUP een bereik van cellen is, moet de functie als matrixformule worden ingevoerd. De eenvoudige voorbeelden hierboven retourneren één overeenkomstige waarde. Als u wilt dat een formule meerdere cellen retourneert, raadpleeg dan de versiespecifieke help en test hoe de betreffende toepassing omgaat met het matrixresultaat.

Hoe kunt u het resultaat controleren?

1. Test of een sleutel bestaat en vergelijk de geretourneerde waarde met de bijbehorende bronregel.
2. Test een sleutel die niet bestaat. Controleer of XLOOKUP de door u gekozen terugvaloptie weergeeft, of dat VLOOKUP het verwachte bericht #N/A of IFNA retourneert.
3. Voor een exacte overeenkomst met VLOOKUP, controleer of het laatste argument 0 of FALSE is. Als u opzettelijk een benaderende overeenkomst gebruikt, controleer dan de sorteervolgorde en de waarden net boven, op en onder een grens.
4. Bij XLOOKUP met een retourkolom links of een omgekeerde zoekopdracht, test u een sleutel met een bekend resultaat en, als er duplicaten zijn, bevestigt u welke instantie moet worden geretourneerd.
5. Sla het werkblad op en open het opnieuw in de oudste versie van Calc of een ander spreadsheetprogramma dat uw ontvangers gebruiken. Een correct resultaat op uw computer bewijst niet dat een oudere versie dezelfde functie ondersteunt.

Voor de meeste nieuwe Calc-werkbladen biedt XLOOKUP duidelijkere bereiken en veiligere standaardwaarden voor exacte overeenkomsten. Gebruik VLOOKUP wanneer achterwaartse compatibiliteit belangrijk is of wanneer de lay-out met de eerste kolom rechts al geschikt is, en maak de overeenkomstmodus expliciet.