Contains lookup

[WhiteDragon]

Zie bijlage, donker oranje wil ik ingevuld hebben door een zo goed mogelijke match uit de tabel rechts
Vlookup werkt niet direct ook niet met A2&"*" aangezien het een inversed text moet zijn, een stukje van de text moet matched met de tabel in groen.

Iemand een idee?

 

[AlexCEL]

Zo?

[NL]B2: =ALS.FOUT(INDEX(H:H;MIN(ALS(ISGETAL(VIND.SPEC($G$2:$G$4;A2));RIJ($G$2:$G$4);9^9)));"")
[EN]B2: =IFERROR(INDEX(H:H;MIN(IF(ISNUMBER(SEARCH($G$2:$G$4;A2));ROW($G$2:$G$4);9^9)));"")

Let op: matrixfunctie, dus afsluiten met Control+Shift+Enter

 

[WhiteDragon]

Nice, hij werkt.
Al snap ik hem nog niet helemaal

Wat is het verschil tussen enter en Ctrl+shift+Enter? Duidelijk is dat de een werkt en de ander niet

 

[WhiteDragon]

En waarom werkt H:H wel en bv $H$2:$H$500 niet?

 

[OctaFish]

Wat is het verschil tussen enter en Ctrl+shift+Enter?

Lees Bericht #2 van Alex nog eens rustig door; duidelijker kun je het niet uitleggen.

 

[AlexCEL]

D.m.v. Ctrl+shift+enter voer je de functie in als matrixfunctie, Excel weet dan dat de functie moet rekenen met een bereik i.p.v. een enkele cel als input. (meer info bijvoorbeeld hier: https://support.office.com/nl-nl/article/matrixformules-richtlijnen-en-voorbeelden-7d94a64e-3ff3-4686-9372-ecfd5caa57c7 of hier http://www.betterwin.nl/know-how/tutorials/excel-matrixformules-basis/).

Korte toelichting:
- met VIND.SPEC/SEARCH wordt per rij uit het groene vak bekeken of het steekwoord voorkomt in A2. Als dit zo is geeft VIND.SPEC een getal als uitkomst (beginpostie van de gevonden tekst). Welk getal is niet belangrijk, wel OF het een getal is of niet.
- ISGETAL resultaat dan in WAAR of ONWAAR. Bij WAAR wordt het regelnummer in de matrix gezet, bij ONWAAR een heel groot getal.
- Als het goed is er maar één overeenkomst, dat is dan de MINimale waarde (zie vorige opmerking). Resultaat is het RIJ-nummer waar de overeenkomst mee gevonden is.
- De INDEX zoekt met het RIJ-nummer de bijbehorende groep op. Daarom werkt H:H wel en H2:H500 niet meteen. Als je deze laatste wilt gebruiken moet je 1 aftrekken van het RIJ-getal. (een INDEX begint altijd bij 1...)