Generička formula
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Sažetak
Za traženje vrijednosti podudaranjem u više stupaca možete upotrijebiti formulu niza koja se temelji na MMULT, TRANSPOSE, COLUMN i INDEX. U prikazanom primjeru formula u H4 je:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
gdje je "imena" imenovani raspon C4: E7, a "grupe" imenovani raspon B4: B7. Formula vraća grupu kojoj pripada svako ime.
Napomena: ovo je formula niza i mora se unijeti s enter control shift.
Obrazloženje
Logički kriteriji koji se koriste u ovoj formuli rade iznutra prema van:
--(names=G4)
gdje je imena imenovani raspon C4: E7. To generira TRUE / FALSE rezultat za svaku vrijednost u podacima, a dvostruki negativ prisiljava TRUE FALSE vrijednosti na 1 i 0 dajući ovakav niz:
(0,0,0;1,0,0;0,0,0;0,0,0)
Ovaj niz sastoji se od 4 retka po 3 stupca, što odgovara strukturi "imena".
Stvara se drugi niz s ovim izrazom:
TRANSPOSE(COLUMN(names)^0))
Funkcija COLUMN koristi se za stvaranje numeričkog polja s 3 stupca i 1 retkom, a TRANSPOSE pretvara ovaj niz u 1 stupac i 3 retka. Podizanje u nulu jednostavno pretvara sve brojeve u nizu u 1. Funkcija MMULT tada se koristi za množenje matrice:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
a rezultirajuće ide u funkciju MATCH kao niz, s 1 kao vrijednost pretraživanja:
MATCH(1,(0;1;0;0),0)
Funkcija MATCH vraća položaj prvog podudaranja, što odgovara retku prvog podudarnog retka koji zadovoljava isporučene kriterije. Ovo se uvodi u INDEX kao broj retka, s imenovanim rasponom "grupe" kao nizom:
=INDEX(groups,2)
Napokon, INDEX vraća "Medvjeda", grupi kojoj pripada Adam.
Doslovno sadrži kriterije
Da biste provjerili postoje li određene tekstualne vrijednosti umjesto točnog podudaranja, možete zajedno koristiti funkcije ISNUMBER i SEARCH. Na primjer, za podudaranje stanica koje sadrže "jabuku" možete koristiti:
=ISNUMBER(SEARCH("apple",data))
Ova je formula ovdje objašnjena.
