Generieke formule
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Samenvatting
Om een waarde op te zoeken door meerdere kolommen te matchen, kunt u een matrixformule gebruiken op basis van de MMULT, TRANSPOSE, COLUMN en INDEX. In het getoonde voorbeeld is de formule in H4:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
waarbij "namen" het benoemde bereik C4: E7 is en "groepen" het benoemde bereik B4: B7. De formule retourneert de groep waartoe elke naam behoort.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control shift enter.
Uitleg
Als je van binnen naar buiten werkt, zijn de logische criteria die in deze formule worden gebruikt:
--(names=G4)
waarbij namen het benoemde bereik C4: E7 is. Dit genereert een TRUE / FALSE-resultaat voor elke waarde in gegevens, en het dubbele negatieve resultaat dwingt de TRUE FALSE-waarden naar 1 en 0 om een array als deze te geven:
(0,0,0;1,0,0;0,0,0;0,0,0)
Deze array is 4 rijen bij 3 kolommen, passend bij de structuur van "namen".
Een tweede array wordt gemaakt met deze uitdrukking:
TRANSPOSE(COLUMN(names)^0))
De COLUMN-functie wordt gebruikt om een numerieke array met 3 kolommen en 1 rij te maken, en TRANSPOSE converteert deze array naar 1 kolom en 3 rijen. Verhogen naar de macht nul converteert eenvoudig alle getallen in de array naar 1. De MMULT-functie wordt vervolgens gebruikt om matrixvermenigvuldiging uit te voeren:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
en het resulterende gaat in de MATCH-functie als de array, met 1 als de opzoekwaarde:
MATCH(1,(0;1;0;0),0)
De functie MATCH retourneert de positie van de eerste overeenkomst, die overeenkomt met de rij van de eerste overeenkomende rij die voldoet aan de opgegeven criteria. Dit wordt in INDEX ingevoerd als het rijnummer, met het benoemde bereik "groepen" als de array:
=INDEX(groups,2)
Ten slotte geeft INDEX "Beer" terug, de groep waar Adam toe behoort.
Letterlijk bevat voor criteria
Om te controleren op specifieke tekstwaarden in plaats van een exacte overeenkomst, kunt u de ISNUMBER- en SEARCH-functies samen gebruiken. Om bijvoorbeeld cellen te matchen die "appel" bevatten, kunt u het volgende gebruiken:
=ISNUMBER(SEARCH("apple",data))
Deze formule wordt hier uitgelegd.
