Generieke formule
=MATCH(1,EXACT(LEFT(A1,255),LEFT(rng,255))*EXACT(MID(A1,256,255),MID(rng,256,255)),0)
Samenvatting
Om tekst langer dan 255 tekens te matchen met de MATCH-functie, kunt u de LEFT-, MID- en EXACT-functies gebruiken om tekst te parseren en te vergelijken, zoals hieronder wordt uitgelegd. In het getoonde voorbeeld is de formule in G5:
=MATCH(1,EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255)),0)
waarbij data het benoemde bereik B5: B15 is.
Opmerking: deze formule voert een hoofdlettergevoelige vergelijking uit.
Uitleg
De MATCH-functie heeft een limiet van 255 tekens voor de opzoekwaarde. Als u langere tekst probeert te gebruiken, retourneert VERGELIJKEN een #VALUE-fout.
Om deze limiet te omzeilen, kunt u booleaanse logica en de LEFT-, MID- en EXACT-functies gebruiken om tekst te parseren en te vergelijken.
De string waarmee we testen in cel E5 is als volgt 373 tekens:
Lorem ipsum dolor amet zet een vogel op het lijstje van trustfonds, eenhoorn vaporware fietsrechten waarvan je waarschijnlijk nog nooit van die snor hebt gehoord. Foerageer helvetica knapperige semiotiek eigenlijk erfstuk. Tumblr poutine eenhoorn godard probeer hard voordat ze uitverkocht narwal meditatie kitsch vest fixie twee letterlijk hoodie retro. Messenger bag hel van knapperig groen sap artisanaal.
In de kern is dit slechts een MATCH-formule, ingesteld om te zoeken naar 1 in de exacte overeenkomstmodus:
=MATCH(1,array,0)
De matrix in de bovenstaande formule bevat alleen 1s en 0s, en 1s staan voor overeenkomende tekst. Deze array wordt geconstrueerd door de volgende uitdrukking:
EXACT(LEFT(E5,255),LEFT(data,255))*EXACT(MID(E5,256,255),MID(data,256,255))
Deze uitdrukking zelf bestaat uit twee delen. Aan de linkerkant hebben we:
EXACT(LEFT(E5,255),LEFT(data,255)) // compare first 255 chars
Hier haalt de functie LINKS de eerste 255 tekens uit E5 en uit alle cellen in de genoemde bereikgegevens (B5: B15). Omdat gegevens 11 tekstreeksen bevatten, genereert LEFT 11 resultaten.
De functie EXACT vergelijkt vervolgens de enkele string van E5 met alle 11 strings die door LINKS worden geretourneerd. EXACT geeft 11 resultaten terug in een array als deze:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Aan de rechterkant hebben we nog een uitdrukking:
EXACT(MID(E5,256,255),MID(data,256,255) // compare next 255 chars
Dit is exact dezelfde benadering als gebruikt met LINKS, maar hier gebruiken we de MID-functie om de volgende 255 tekens tekst te extraheren. De EXACTE functie retourneert opnieuw 11 resultaten:
(TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
Wanneer de twee bovenstaande arrays met elkaar worden vermenigvuldigd, worden de WARE FALSE-waarden door de wiskundige bewerking omgezet in 1s en 0s. Volgens de regels van booleaanse rekenkunde is het resultaat een array als deze:
(0;0;0;0;0;0;0;0;0;1;0)
die rechtstreeks naar MATCH wordt geretourneerd als de opzoekmatrix. De formule kan nu worden omgezet in:
=MATCH(1,(0;0;0;0;0;0;0;0;0;1;0),0)
De MATCH-functie voert een exacte match uit en retourneert het eindresultaat van 10, wat de tiende tekstreeks in B5: B15 vertegenwoordigt.
Let op: de tekstlengte in het voorbeeld wordt berekend met de LEN-functie. Het verschijnt alleen ter referentie.
Hoofdletterongevoelige optie
De functie EXACT is hoofdlettergevoelig, dus de bovenstaande formule houdt rekening met hoofdletters en kleine letters.
Om een hoofdletterongevoelige overeenkomst met lange tekst uit te voeren, gebruikt u de ISNUMBER- en SEARCH-functies als volgt:
=MATCH(1,ISNUMBER(SEARCH(LEFT(E5,255),LEFT(data,255)))*ISNUMBER(SEARCH(MID(E5,256,255),MID(data,256,255))),0)
De algemene structuur van deze formule is identiek aan het bovenstaande voorbeeld, maar de SEARCH-functie wordt gebruikt in plaats van EXACT om tekst te vergelijken (hier in detail uitgelegd).
In tegenstelling tot EXACT ondersteunt de SEARCH-functie ook jokertekens.
Met XMATCH
De XMATCH-functie heeft niet dezelfde limiet van 255 tekens als MATCH. Om een vergelijkbare overeenkomst uit te voeren op lange tekst met XMATCH, kunt u de veel eenvoudigere formule hieronder gebruiken:
=XMATCH(E5,data)
Opmerking: XMATCH ondersteunt jokertekens, maar is niet hoofdlettergevoelig.
