Generieke formule
=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")
Samenvatting
Een manier om meerdere overeenkomsten in Excel te extraheren, is door INDEX en MATCH te gebruiken met een hulpkolom die overeenkomende gegevens markeert. Dit vermijdt de complexiteit van een meer geavanceerde matrixformule. In het getoonde voorbeeld is de formule in H6:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
waarbij ct (G3), data (B3: E52) en helper (E3: E52) benoemde bereiken zijn.
Uitleg
De uitdaging met opzoekformules die meer dan één overeenkomst ophalen, is het beheren van duplicaten (dwz meerdere overeenkomsten). Opzoekformules zoals VERT.ZOEKEN en INDEX + WEDSTRIJD kunnen gemakkelijk de eerste overeenkomst vinden, maar het is veel moeilijker om "alle overeenkomsten" op te zoeken wanneer criteria meer dan één overeenkomst vinden.
Deze formule lost deze uitdaging op door een hulpkolom te gebruiken die een numerieke waarde retourneert die kan worden gebruikt om eenvoudig meerdere overeenkomsten te extraheren. De formule in de hulpkolom ziet er als volgt uit:
=SUM(E2,AND(C3=$I$3,D3=$J$3))
De hulpkolom test elke rij in de gegevens om te zien of de afdeling in kolom C overeenkomt met de waarde in I3 en het gebouw in kolom D overeenkomt met de waarde in J3. Beide logische tests moeten WAAR retourneren om AND WAAR te laten retourneren.
Voor elke rij wordt het resultaat van de EN-functie opgeteld bij de "waarde hierboven" in de hulpkolom om een telling te genereren. Het praktische effect van deze formule is een oplopende teller die alleen verandert als er een (nieuwe) match wordt gevonden. De waarde blijft dan hetzelfde totdat de volgende overeenkomst is gevonden. Dit werkt omdat de TRUE / FALSE-resultaten die worden geretourneerd door AND, worden gedwongen tot 1/0-waarden als onderdeel van de sombewerking. VALSE resultaten voegen niets toe, en WAAR resultaten voegen 1 toe.
Terug in het extractiegebied ziet de opzoekformule voor Naam in kolom H er als volgt uit:
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
Werkend van binnen naar buiten, zoekt het INDEX + MATCH-gedeelte van de formule de naam op voor de eerste gevonden overeenkomst, met het rijnummer in kolom G als de overeenkomstwaarde:
INDEX(data,MATCH($G6,helper,0),1)
INDEX ontvangt alle drie de kolommen met gegevens als de array (met de naam bereik "data"), en MATCH is geconfigureerd om overeen te komen met het rijnummer in de helperkolom (het benoemde bereik "helper") in exacte match-modus (3e argument ingesteld op nul) .
Dit is waar de slimheid van de formule duidelijk wordt. De helperkolom bevat duidelijk duplicaten, maar het maakt niet uit, omdat MATCH alleen overeenkomt met de eerste waarde. Elke "eerste waarde" komt per ontwerp overeen met de juiste rij in de gegevenstabel.
De formules in de kolommen I en J zijn hetzelfde als H, behalve het kolomnummer, dat telkens met één wordt verhoogd.
De IF-instructie die de INDEX / MATCH-formule omhult, voert een eenvoudige functie uit: het controleert elk rijnummer in het extractiegebied om te zien of het rijnummer kleiner is dan of gelijk is aan de waarde in G3 (genaamd bereik "ct"), dat is het totale aantal overeenkomende records. Als dit het geval is, wordt de INDEX / MATCH-logica uitgevoerd. Als dit niet het geval is, voert IF een lege string ("") uit.
De formule in G3 (genaamd bereik "ct") is eenvoudig:
=MAX(helper)
Omdat de maximale waarde in de helperkolom hetzelfde is als het totale aantal overeenkomsten, is de MAX-functie alles wat we nodig hebben.
Opmerking: het extractiegebied moet handmatig worden geconfigureerd om zoveel gegevens te verwerken als nodig is (dwz 5 rijen, 10 rijen, 20 rijen, enz.). In dit voorbeeld is het beperkt tot 5 rijen om het werkblad compact te houden.
Ik heb deze techniek geleerd in het boek Control + Shift + Enter van Mike Girvin.
De FILTER-functie
Als je de Dynamic Array-versie van Excel hebt, is de FILTER-functie een stuk eenvoudiger om alle overeenkomende gegevens te extraheren.
