Splošna formula
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Povzetek
Za iskanje vrednosti z INDEX in MATCH z več merili lahko uporabite matrično formulo. V prikazanem primeru je formula v H8:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Opomba: to je formula matrike, ki jo je treba vnesti s tipko control + shift + enter, razen v Excelu 365.
Pojasnilo
To je naprednejša formula. Za osnove glejte Kako uporabljati INDEX in MATCH.
Običajno je formula INDEX MATCH konfigurirana z nastavitvijo MATCH, da gleda skozi obseg enega stolpca in zagotavlja ujemanje na podlagi danih meril. Brez združevanja vrednosti v pomožnem stolpcu ali v sami formuli ni mogoče navesti več kot enega merila.
Ta formula deluje okoli te omejitve z uporabo logične logike za ustvarjanje nabora enot in ničel, ki predstavljajo vrstice, ki se ujemajo z vsemi 3 merili, nato pa z uporabo MATCH ustreza prvemu 1 najdenemu. S tem delčkom se ustvari začasno polje enot in ničel:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Tu primerjamo izdelek v H5 z vsemi predmeti, velikost v H6 z vsemi velikostmi in barvo v H7 z vsemi barvami. Začetni rezultat so trije nizi TRUE / FALSE rezultatov, kot je ta:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Nasvet: za prikaz teh rezultatov uporabite F9. Preprosto izberite izraz v vrstici s formulami in pritisnite F9.
Matematična operacija (množenje) pretvori vrednosti TRUE FALSE v 1s in 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Po množenju imamo eno samo matriko:
(0;0;1;0;0;0;0)
ki se vnese v funkcijo MATCH kot iskalno polje z vrednostjo iskanja 1:
MATCH(1,(0;0;1;0;0;0;0))
V tem trenutku je formula standardna formula INDEX MATCH. Funkcija MATCH vrne 3 v INDEX:
=INDEX(E5:E11,3)
in INDEX vrne končni rezultat 17,00 USD.
Vizualizacija matrike
Zgoraj razložene nize je težko vizualizirati. Spodnja slika prikazuje osnovno idejo. Stolpci B, C in D ustrezajo podatkom v primeru. Stolpec F se ustvari z množenjem treh stolpcev. To je matrika, predana MATCH.
Različica brez matrike
V to formulo je mogoče dodati še INDEX, pri čemer se izognete potrebi po vnosu kot matrično formulo s kontrolnikom + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Funkcija INDEX lahko izvorno obdeluje nize, zato je drugi INDEX dodan samo, da "ujame" matriko, ustvarjeno z logično logično operacijo, in vrne isto matriko spet v MATCH. Za to je INDEX nastavljen z nič vrsticami in enim stolpcem. Trik z ničelno vrstico povzroči, da INDEX vrne stolpec 1 iz polja (ki je že tako ali tako en stolpec).
Zakaj bi želeli ne-matrično različico? Včasih ljudje pozabijo vnesti matrično formulo s kontrolnikom + shift + enter in formula vrne napačen rezultat. Torej, formula brez matrike je bolj "neprebojna". Vendar je kompromis bolj zapletena formula.
Opomba: V Excelu 365 ni treba na poseben način vnašati formule matrike.
