Formula generică
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
rezumat
Pentru a căuta o valoare prin potrivirea între mai multe coloane, puteți utiliza o formulă matrice bazată pe MMULT, TRANSPOSE, COLUMN și INDEX. În exemplul prezentat, formula din H4 este:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
unde „nume” este gama numită C4: E7, iar „grupuri” este gama numită B4: B7. Formula returnează grupul căruia îi aparține fiecare nume.
Notă: aceasta este o formulă de matrice și trebuie introdusă cu control shift shift enter.
Explicaţie
Lucrând din interior spre exterior, criteriile logice utilizate în această formulă sunt:
--(names=G4)
unde numele este intervalul numit C4: E7. Acest lucru generează un rezultat TRUE / FALSE pentru fiecare valoare din date, iar negativul dublu constrânge valorile TRUE FALSE la 1 și 0 pentru a produce o matrice de acest fel:
(0,0,0;1,0,0;0,0,0;0,0,0)
Această matrice are 4 rânduri pe 3 coloane, care se potrivesc cu structura „nume”.
O a doua matrice este creată cu această expresie:
TRANSPOSE(COLUMN(names)^0))
Funcția COLUMN este utilizată pentru a crea o matrice numerică cu 3 coloane și 1 rând, iar TRANSPOSE convertește această matrice în 1 coloană și 3 rânduri. Creșterea la puterea zero convertește pur și simplu toate numerele din matrice în 1. Funcția MMULT este apoi utilizată pentru a efectua multiplicarea matricei:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
iar rezultatul intră în funcția MATCH ca matrice, cu 1 ca valoare de căutare:
MATCH(1,(0;1;0;0),0)
Funcția MATCH returnează poziția primei potriviri, care corespunde rândului primului rând care corespunde criteriilor furnizate. Acesta este introdus în INDEX ca număr de rând, cu gama numită „grupuri” ca matrice:
=INDEX(groups,2)
În cele din urmă, INDEX returnează „Ursul”, grupul din care face parte Adam.
Literalul conține criterii
Pentru a verifica valorile textului specific în loc de o potrivire exactă, puteți utiliza împreună funcțiile ISNUMBER și SEARCH. De exemplu, pentru a se potrivi celulelor care conțin „măr” puteți utiliza:
=ISNUMBER(SEARCH("apple",data))
Această formulă este explicată aici.
