Generieke formule
=LOOKUP(2,1/SEARCH(things,A1),things)
Samenvatting
Als u een cel op een van de verschillende dingen wilt controleren en de laatst gevonden overeenkomst in de lijst wilt retourneren, kunt u een formule gebruiken op basis van de functies ZOEKEN en ZOEKEN. Als er meerdere overeenkomsten zijn gevonden, retourneert de formule de laatste overeenkomst uit de lijst met "dingen".
In het getoonde voorbeeld is de formule in C5:
=LOOKUP(2,1/SEARCH(things,B5),things)
Uitleg
Context: je hebt een lijst met dingen in het benoemde bereik "dingen" (E5: E8), en je wilt cellen in kolom B controleren om te zien of ze deze dingen bevatten. Als dit het geval is, wilt u het laatste item van "dingen" retourneren dat is gevonden.
In deze formule wordt de functie SEARCH gebruikt om cellen in kolom B als volgt te doorzoeken:
SEARCH(things,B5)
Wanneer SEARCH een overeenkomst vindt, retourneert het de positie van de overeenkomst in de cel die wordt doorzocht. Als de zoekopdracht geen overeenkomst kan vinden, retourneert het de fout #VALUE. Omdat we SEARH meer dan één ding geven om naar te zoeken, zal het meer dan één resultaat opleveren. In het getoonde voorbeeld retourneert SEARCH een reeks resultaten zoals deze:
(8;24;#VALUE!;#VALUE!)
Deze array wordt vervolgens gebruikt als deler voor het getal 1. Het resultaat is een array met fouten en decimale waarden. De fouten vertegenwoordigen dingen die niet zijn gevonden en de decimale waarden vertegenwoordigen dingen die zijn gevonden. In het getoonde voorbeeld ziet de array er als volgt uit:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Deze array dient als de "lookup_vector" voor de functie ZOEKEN. De opzoekwaarde wordt geleverd als het nummer 2 en de resultaatvector is het benoemde bereik "dingen". Dit is het slimme deel.
De formule is zo opgebouwd dat de lookup-vector nooit een waarde groter dan 1 bevat, terwijl de lookup-waarde 2 is. Dit betekent dat de lookup-waarde nooit zal worden gevonden. In dit geval komt ZOEKEN overeen met de laatste numerieke waarde die in de array is gevonden, die overeenkomt met het laatste "ding" dat is gevonden door SEARCH.
Tenslotte, gebruikmakend van het benoemde bereik "dingen" geleverd als de resultaatvector, geeft ZOEKEN het laatst gevonden item terug.
Met hardgecodeerde waarden
Door een bereik als "dingen" te gebruiken, is het gemakkelijk om de lijst met zoektermen aan te passen (en meer zoektermen toe te voegen), maar dat is geen vereiste. U kunt waarden ook als volgt rechtstreeks in de formule coderen:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
