Indice potrivire rânduri și coloane cu criterii multiple
Cu toții folosim VLOOKUP zi de zi pentru a prelua datele și, de asemenea, suntem conștienți de faptul că VLOOKUP poate prelua datele de la stânga la dreapta, astfel încât valoarea de căutare ar trebui să fie întotdeauna în partea stângă a coloanelor rezultate. Cu toate acestea, avem mai multe alternative care pot fi utilizate ca alternativă la funcția VLOOKUP în Excel. Cu tehnologia avansată, putem utiliza aceste formule INDEX + MATCH pentru a se potrivi cu mai multe criterii pentru rânduri și coloane. Deci, acest articol special vă va prezenta în detaliu despre această tehnică.
Cum se folosește formula INDEX + MATCH pentru a se potrivi cu mai multe criterii?
Aici vă explicăm cum să utilizați formula index + potrivire pentru a potrivi mai multe criterii pentru rânduri și coloane cu exemple.
Exemplul # 1 - Formula INDEX + MATCH
Nu majoritatea funcțiilor de căutare a utilizatorilor Excel dincolo de VLOOKUP, motivele ar putea fi atât de multe. Oricum, hai să avem o introducere simplă la această formulă înainte de a trece la nivelul avansat.
De exemplu, uitați-vă la structura de date de mai jos în Excel.
Avem nume de „reprezentant de vânzări” și valorile lor de vânzare respective. Pe de altă parte, avem o listă derulantă cu „Reprezentare vânzare” în celula D2.
Pe baza selecției pe care o facem din lista derulantă, valoarea vânzărilor trebuie să apară în celula E2.
Problema este că nu putem aplica formula VLOOKUP deoarece valoarea de căutare „Sales Rep” este în dreapta coloanei de rezultate „Sales”, deci, în aceste cazuri, putem folosi formula combinată a valorii căutării INDEX + MATCH.
INDEX caută valoarea numărului de rând menționat în intervalul A2: A11 și, în acest interval, trebuie să furnizăm din ce rând trebuie să provină valoarea vânzărilor. Această valoare de rând se bazează pe numele „Reprezentant vânzări” selectat în lista derulantă în excel, astfel încât funcția MATCH caută numărul rândului „Reprezentant vânzări” din intervalul B2: B11 și returnează numărul rândului valorii potrivite .
Exemplul # 2 - Criterii multiple în formula INDEX + MATCH
Acum avem o structură de date ca cea de mai jos.
Avem valori de vânzări lunare pentru „Reprezentant vânzări”. Din acest tabel, avem nevoie de rezultate dinamice ca în celula A15, am creat o listă derulantă „Sales Rep”, iar în celula B14 am creat o listă derulantă „Lună”.
Pe baza selecției făcute în aceste două celule, formula noastră trebuie să preia datele din tabelul de mai sus.
De exemplu, dacă aleg „Rep 8” și „Apr” atunci trebuie să afișeze valoarea de vânzare a „Rep 8” pentru luna „Apr.”
Deci, în aceste cazuri, trebuie să potrivim atât rândurile, cât și coloanele. Urmați pașii de mai jos pentru a aplica formula care să se potrivească atât cu rândurile, cât și cu coloanele.
Pasul 1: Deschideți funcția INDEX în celula B15.
Pasul 2: Primul argument al funcției INDEX este „Array”, adică din ce gamă de celule avem nevoie de rezultat. Deci, în acest caz, avem nevoie de valori de vânzare, deci alegeți intervalul de celule de la B2 la G11.
Pasul 3: Următorul argument al funcției INDEX din care rând din gama selectată avem nevoie de rezultat. În acest caz, trebuie să ajungem la numărul rândului „Sales Rep” pe baza selecției făcute în celula derulantă celula A15. Deci, pentru a prelua dinamic numărul rândului pe baza funcției de selecție deschise MATCH.
Pasul 4: VALOAREA CĂUTĂRII funcției MATCH este „Reprezentant vânzări”, deci alegeți celula A15 ca referință.
Pasul 5: Aranjamentul de căutare va fi intervalul de nume „Reprezentant vânzări” din tabelul principal. Așa că alegeți intervalul între A2 și A11.
Pasul 6: Tipul de potrivire a funcției MATCH va fi exact, așa că introduceți zero ca valoare a argumentului.
Pasul 7: Următorul argument al funcției INDEX este „Număr coloană”, adică din intervalul selectat de celule din care coloană avem nevoie de rezultat. Acest lucru depinde de luna pe care o selectăm din lista derulantă a celulei B14. Deci, pentru a obține automat numărul coloanei, deschideți o altă funcție MATCH.
Pasul 8: Această valoare de căutare de timp va fi numele lunii, deci selectați celula B14 ca referință.
Pasul 9: matricea de căutare va fi intervalul lunar de celule din tabelul principal, adică de la B1 la G1.
Pasul 10: Ultimul argument este tipul de potrivire; alegeți „Exact Match” ca criteriu. Închideți două paranteze și apăsați tasta Enter pentru a obține rezultatul.
După cum putem vedea mai sus, am ales „Rep 6” și „Apr” ca lună, iar formula noastră a returnat valoarea vânzărilor pentru luna „Apr” pentru „Rep 6”.
Notă: Celula de culoare galbenă este referința pentru dvs.
Lucruri de amintit
- O combinație de INDEX + MATCH poate fi mai puternică decât formula VLOOKUP.
- INDEX & MATCH poate potrivi atât rândurile, cât și antetele coloanelor și poate returna rezultatul din tabelul din mijloc.
- MATCH poate returna numărul rândului și numărul coloanei din antetele tabelelor ambelor rânduri și coloane.
