Generieke formule
=XLOOKUP("*"&value&"*",lookup,results,,2)
Samenvatting
Als u XLOOKUP wilt gebruiken om waarden te zoeken die specifieke tekst bevatten, kunt u jokertekens en aaneenschakeling gebruiken. In het getoonde voorbeeld is de formule in F5:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
waarbij code (B5: B15) en hoeveelheid (C5: C15) benoemde bereiken zijn.
Uitleg
De XLOOKUP-functie bevat ingebouwde ondersteuning voor jokertekens, maar deze functie moet expliciet worden ingeschakeld door de matchmodus in te stellen op het nummer 2.
In het weergegeven voorbeeld is XLOOKUP geconfigureerd om overeen te komen met de waarde die is ingevoerd in cel E5, die overal in de opzoekwaarden in B5: B15 kan voorkomen. De formule in F5 is:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, met asterisken (*) aaneengeschakelde voor- en achterkant
- opzoekmatrix - het benoemde bereik code (B5: B15)
- return_array - het benoemde bereik hoeveelheid (C5: C15)
- if_not_found - de string "geen overeenkomst"
- match_mode - opgegeven als 2 (wildcard-overeenkomst)
- search_mode - niet opgegeven. Standaard ingesteld op 1 (eerste tot laatste)
Om een overeenkomst van het type "bevat" automatisch te maken, wordt het jokerteken asterisk (*) zowel voorafgegaan als toegevoegd aan de waarde in cel E5 met aaneenschakeling:
"*"&E5&"*"
Na aaneenschakeling wordt de formule:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP lokaliseert de eerste overeenkomst die "BCC" (050-BCC-123 in rij 10) bevat en retourneert de corresponderende waarde uit de retourmatrix, 50.
Merk op dat XLOOKUP niet hoofdlettergevoelig is, het invoeren van "bcc" in E5 zal hetzelfde resultaat opleveren:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Zie hieronder voor een optie om XLOOKUP te configureren voor een hoofdlettergevoelige overeenkomst.
VERT.ZOEKEN optie
De VERT.ZOEKEN-formule ondersteunt ook jokertekens als deze exact overeenkomen. De equivalente VERT.ZOEKEN-formule voor dit voorbeeld is:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Volledige uitleg hier.
Met ZOEKEN en VINDEN
Het is ook mogelijk om de functies ZOEKEN en ZOEKEN te gebruiken om een overeenkomst van het type "bevat" uit te voeren met XLOOKUP. Voor een niet-hoofdlettergevoelige overeenkomst (zoals in het bovenstaande voorbeeld), kunt u ZOEKEN als volgt gebruiken:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Voor een hoofdlettergevoelige overeenkomst kunt u in plaats daarvan FIND gebruiken:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Beide bovenstaande opties maken het gemakkelijker om criteria uit te breiden met andere voorwaarden met behulp van booleaanse logica.
De logica voor ISNUMBER + SEARCH wordt hier uitgelegd.
Meerdere overeenkomsten
Zie de FILTER-functie als je meerdere overeenkomsten nodig hebt.
