În funcția VLOOKUP, definim adesea col_index_no static. Îl codificăm în cadrul formulei VLOOKUP, cum ar fi VLOOKUP (id, date,3, 0). Problema apare atunci când inserăm sau ștergem o coloană în cadrul datelor. Dacă eliminăm sau adăugăm o coloană înainte sau după a 3-a coloană, a 3-a coloană nu se va mai referi la coloana intenționată. Aceasta este o problemă. Alt lucru este atunci când aveți mai multe coloane de căutat. Va trebui să editați indexul coloanei din fiecare formulă. Copierea simplă nu va ajuta.
Dar ce zici, dacă îi poți spune VLOOKUP să se uite la titluri și să returneze doar valoarea titlurilor potrivite. Aceasta se numește VLOOKUP bidirecțional.
De exemplu, dacă am o formulă VLOOKUP pentrumărci coloană, atunci VLOOKUP ar trebui să caute mărci coloană în date și returnează valoarea din acea coloană. Aceasta ne va rezolva problema.
Hmm … Bine, deci cum facem asta? Prin utilizarea funcției Match în funcția VLOOKUP.
Formula generică
=CĂUTARE(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Lookup_value: valoarea de căutare din prima coloană a table_array.
Table_array: intervalul în care doriți să efectuați o căutare. De exemplu, A2, D10.
Lookup_heading: antetul pe care doriți să îl căutați în titlurile table_array.
Head_headings: Referință a titlurilor din matricea de tabele. De exemplu. dacă tabelul este A2, D10 și titlurile din partea de sus a fiecărei coloane, atunci este A1: D1.
Deci, acum știm de ce avem nevoie pentru col_index dinamic, să clarificăm totul cu un exemplu.
Exemplu VLOOKUP dinamic
Pentru acest exemplu, avem acest tabel care conține date despre studenți în intervalul A4: E16.
Folosind rola nu și titlul, doresc să extrag date din acest tabel. Pentru acest exemplu, în celula H4, vreau să obțin date despre rolul nr scris în celula G4 și despre titlul din H3. Dacă schimb antetul, datele din intervalul respectiv ar trebui recuperate în celula H4.
Scrieți această formulă în celula H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Deoarece matricea noastră de tabele este B4: E16, matricea noastră de titluri devine B3: E3.
Notă: Dacă datele dvs. sunt bine structurate, atunci titlurile de coloană vor avea același număr de coloane și este primul rând din tabel.
Cum functioneaza:
Deci, partea principală este evaluarea automată a numărului de index al coloanei. Pentru a face acest lucru, am folosit funcția MATCH.
MATCH (H3, B3: E3,0): Deoarece H3 conține „student”, MATCH va returna 2. Dacă H3 ar avea „Nota”, ar fi returnat 4 și așa mai departe. Formula VLOOKUP va avea în sfârșit col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
După cum știm, funcția MATCH returnează numărul de index al unei valori date în intervalul unidimensional furnizat. Prin urmare, MATCH va căuta orice valoare scrisă în H3 în intervalul B3: E3 și va returna numărul său de index.
Acum, ori de câte ori veți schimba titlul în H3, dacă este în titluri, această formulă va returna o valoare din coloana respectivă. În caz contrar, veți avea o eroare # N / A.
CĂUTARE rapidă în mai multe coloane
În exemplul de mai sus, aveam nevoie de răspunsul dintr-o valoare de coloană. Dar dacă doriți să obțineți mai multe coloane simultan. Dacă copiați formula de mai sus, aceasta va returna erorile. Trebuie să facem câteva modificări minore pentru a-l face portabil.
Utilizarea referințelor absolute cu VLOOKUP
Scrieți mai jos formula în celula H2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Acum copiați H2 în toate celulele din intervalul H2: J6 pentru a-l umple cu date.
Cum functioneaza:
Aici am dat referință absolută din fiecare interval, cu excepția rândului din valoarea de căutare pentru VLOOKUP ($ G2) și coloana în lookup_value pentru MATCH (H $ 1).
$ G2: Acest lucru va permite rândului să se schimbe pentru valoarea de căutare pentru funcția VLOOKUP în timp ce copiați în jos, dar restricționează modificarea coloanei când este copiat în dreapta. Ceea ce va face ca VLOOKUP să caute Id din coloana G numai cu rândul relativ.
În mod similar, H $ 1 va permite coloanei să se schimbe atunci când este copiată pe orizontală și să restricționeze rândul atunci când este copiat în jos.
Utilizarea intervalelor numite
Exemplul de mai sus funcționează bine, dar devine dificil să citești și să scrii această formulă. Și acest lucru nu este deloc portabil. Acest lucru poate fi simplificat folosind intervale denumite.
Mai întâi vom face câteva denumiri aici. Pentru acest exemplu, am numit
$ B $ 2: $ E $ 14: ca Date
$ B $ 1: $ E $ 1: ca titluri
H $ 1: Denumiți-l drept Heading. Faceți coloanele relative. Pentru aceasta, selectați H1. Apăsați CTRL + F3, faceți clic pe nou, în Se referă la secțiunea eliminați „$” din partea din față a H.
$ G2: În mod similar, denumiți-l ca RollNo. De această dată, rândul este relativ, eliminând „$” din partea din față a lui 2.
Acum, când aveți toate numele pe foaie, scrieți această formulă oriunde în fișierul Excel. Va primi întotdeauna răspunsul corect.
= VLOOKUP (RollNo, Data, MATCH (Heading, Headings, 0), 0)
Vedeți, oricine poate citi acest lucru și îl poate înțelege.
Deci, folosind aceste metode, puteți face dinamic col_index_num. Spuneți-mi dacă acest lucru a fost util în secțiunea de comentarii de mai jos.
Cum se folosește tFuncția VLOOKUP în Excel
Referință relativă și absolută în Excel
Gama denumită în Excel
Cum să căutați din diferite foi Excel
VLOOKUP Valori multiple
Articole populare
50 Comandă rapidă Excel pentru a vă crește productivitatea : Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se folosește tFuncția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.