Este ușor să căutați valoare cu o cheie unică într-un tabel. Putem folosi pur și simplu funcția VLOOKUP. Dar când nu aveți acea coloană unică în datele dvs. și trebuie să căutați în mai multe coloane pentru a se potrivi cu o valoare, VLOOKUP nu vă ajută.
Deci, pentru a căuta o valoare într-un tabel cu mai multe criterii, vom folosi formula INDEX-MATCH-INDEX.
Formula generică pentru căutarea mai multor criterii
= INDEX (interval de căutare, MATCH (1, INDEX ((criterii1 = interval1) * (criterii2 = interval2) * (criteriiN = intervalN), 0,1), 0))
interval_consultare: Este intervalul din care doriți să recuperați valoarea.
Criterii 1, Criterii 2, Criterii N: Acestea sunt criteriile pe care doriți să le potriviți în intervalul 1, intervalul 2 și intervalul N. Puteți avea până la 270 de criterii - perechi de intervale.
Range1, range2, rangeN: Acestea sunt intervalele în care veți corespunde criteriilor dvs. respective.
Cum va funcționa? Să vedem…
INDEX și MATCH cu exemplu de criterii multiple
Aici am un tabel de date. Vreau să extrag numele clientului folosind Data de rezervare, Constructorul și Zona. Așadar, aici am trei criterii și un interval de căutare.
Scrieți această formulă în celula I4 apăsați Enter.
= INDEX (E2: E16, MATCH (1, INDEX ((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1), 0))
Cum functioneaza:
Știm deja cum funcționează funcția INDEX și MATCH în EXCEL, deci nu voi explica asta aici. Vom vorbi despre trucul pe care l-am folosit aici.
(I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16): Partea principală este aceasta. Fiecare parte a acestei afirmații returnează o serie de false false.
Când valorile booleene sunt înmulțite, ele returnează matricea de 0 și 1. Multiplicarea funcționează ca operator ȘI. Intensificați atunci când toate valorile sunt adevărate numai atunci returnează încă 1
(I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16) Acest lucru se va întoarce
{FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; ADEVARE; ADEVĂRATE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE} * {FALSE; FALSE; FALSE; ADEVATE; ADEVATE; ADEVATE; ADEVATE;; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE} * {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Ceea ce se va traduce în
{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}
INDEX((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1): Funcția INDEX va returna aceeași matrice ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) la funcția MATCH ca matrice de căutare.
MECI(1,INDEX((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1): funcția MATCH va căuta 1 în matricea {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. Și va returna numărul index al primului 1 găsit în matrice. Care este 8 aici.
INDEX(E2: E16,MECI(1,INDEX((I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0,1), 0)): În cele din urmă, INDEX va returna valoarea din intervalul dat (E2: E16) la găsit index (8).
Simplu????. Ne pare rău, nu am putut face acest lucru mai simplu.
Soluție de matrice
Dacă puteți apăsa CTRL + SHIFT + ENTER în consecință, atunci puteți elimina funcția interioară INDEX. Scrieți doar această formulă și apăsați CTRL + SHIFT + ENTER.
= INDEX (E2: E16, MATCH (1, (I1 = A2: A16) * (I2 = B2: B16) * (I3 = C2: C16), 0))
Formula de matrice generică pentru căutarea mai multor criterii
= INDEX (interval de căutare, MATCH (1, (criterii1 = interval1) * (criterii2 = interval2) * (criteriiN = intervalN), 0))
Formula funcționează la fel ca explicația de mai sus.
Am încercat din răsputeri să-l explic cât mai simplu posibil. Dar dacă nu am fost suficient de clar, anunțați-l în secțiunea de comentarii de mai jos. Apropo, nu trebuie să știți cum funcționează motorul pentru a conduce o mașină. Trebuie doar să știi cum să o conduci. Și o știi foarte bine.
Cum să căutați primele 5 valori cu valori duplicate folosind INDEX-MATCH în Excel
Cum să căutați mai multe valori în Excel
Cum să VLOOKUP cu index dinamic de col în Excel
Cum se utilizează VLOOKUP din două sau mai multe tabele de căutare în Excel
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea
Cum se utilizează funcția VLOOKUP în Excel
Cum se utilizează funcția COUNTIF în Excel
Cum se utilizează funcția SUMIF în Excel