Dit is een meer geavanceerde formule. Zie voor de basis hoe INDEX en MATCH te gebruiken.
normaal gesproken wordt een INDEX MATCH formule geconfigureerd met MATCH set om door een één-kolom bereik te kijken en een match te bieden op basis van bepaalde criteria. Zonder waarden in een helperkolom of in de formule zelf samen te voegen, is er geen manier om meer dan één criterium te leveren.,
deze formule werkt rond deze beperking door Booleaanse logica te gebruiken om een array van enen en nullen te maken om rijen te representeren die aan alle 3 criteria voldoen, en vervolgens MATCH te gebruiken om de eerste gevonden 1 te matchen. De tijdelijke array van enen en nullen wordt gegenereerd met dit fragment:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)Hier vergelijken we het item in H5 met alle items, de grootte in H6 met alle groottes en de kleur in H7 met alle kleuren. Het initiële resultaat is drie arrays van TRUE/FALSE resultaten zoals deze:
{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}*{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}Tip: Gebruik F9 om deze resultaten te zien., Selecteer gewoon een expressie in de formulebalk en druk op F9.
De wiskundige bewerking (vermenigvuldiging) transformeert de WARE VALSE waarden, 1s en 0s:
{1;1;1;0;0;0;1}*{0;0;1;0;0;1;0}*{1;0;1;0;0;0;1}Na vermenigvuldiging, we hebben een matrix, zoals dit:
{0;0;1;0;0;0;0}die is ingevoerd in de MATCH functie als de lookup-array met een lookup-waarde van 1:
MATCH(1,{0;0;1;0;0;0;0})Op dit punt, de formule is een standaard INDEX MATCH formule., De MATCH functie geeft 3 terug naar INDEX:
=INDEX(E5:E11,3)en INDEX geeft een eindresultaat van $17.00.
Array visualisatie
de arrays hierboven uitgelegd kan moeilijk te visualiseren zijn. De afbeelding hieronder toont het basisidee. De kolommen B, C en D komen overeen met de gegevens in het voorbeeld. Kolom F wordt gecreëerd door de drie kolommen samen te vermenigvuldigen. Het is de array overhandigd om overeen te komen.,
Non-array versie
Het is mogelijk om een andere INDEX voor deze formule, het vermijden van de noodzaak voor het invoeren als een matrixformule met control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))De functie INDEX kan omgaan met arrays native, dus de tweede INDEX is toegevoegd om te “vangen” de matrix gemaakt met de booleaanse logica werking en terug dezelfde matrix opnieuw aan te passen. Om dit te doen, INDEX is geconfigureerd met nul rijen en één kolom. De nul rij truc zorgt ervoor dat INDEX kolom 1 retourneert uit de array (die toch al één kolom is).,
waarom zou u de niet-array versie willen? Soms vergeten mensen een matrixformule in te voeren met control + shift + enter en retourneert de Formule Een onjuist resultaat. Dus, een niet-matrixformule is meer “kogelvrij”. De afweging is echter een complexere formule.
opmerking: in Excel 365 is het niet nodig om matrixformules op een speciale manier in te voeren.