VLOOKUP cu MATCH | Creați o formulă flexibilă cu VLOOKUP MATCH

Formula Vlookup funcționează numai atunci când tabela din formulă nu se modifică, dar dacă există o coloană nouă inserată în tabel sau o coloană este ștearsă, formula dă un rezultat incorect sau reflectă o eroare, pentru a face formula fără erori în astfel de situații dinamice folosim funcția de potrivire pentru a se potrivi efectiv cu indexul datelor și pentru a returna rezultatul real.

Combinați VLOOKUP cu Match

Formula vlookup este cea mai frecvent utilizată funcție care este utilizată pentru a căuta și returna fie aceeași valoare în indexul de coloană specificat, fie valoarea dintr-un alt index de coloană cu referire la valoarea potrivită din prima coloană. Provocarea majoră cu care se confruntă în timpul utilizării vlookup este aceea că indexul de coloană care trebuie specificat este static și nu are o funcționalitate dinamică. Mai ales atunci când lucrați la mai multe criterii care necesită modificarea manuală a indexului coloanei de referință. Prin urmare, această nevoie este îndeplinită prin utilizarea formulei „MATCH” pentru a avea o mai bună aderență sau control asupra indexului de coloane care se schimbă frecvent în formula VLOOKUP.

Formula de căutare și potrivire V

# 1 - Formula VLOOKUP

Formula funcției VLOOKUP în Excel

Aici toate argumentele care trebuie introduse sunt obligatorii.

  • Lookup_value - Aici trebuie introdusă celula de referință sau textul cu ghilimele duble pentru a fi identificate în intervalul de coloane.
  • Matrice de tabele -   Acest argument necesită introducerea intervalului de tabelă în care trebuie căutată Look__value și datele de recuperat se află în intervalul de coloane special.
  • Col_index_num - În acest argument, trebuie introdus numărul indexului coloanei sau numărul coloanei din prima coloană de referință, din care valoarea corespunzătoare trebuie extrasă din aceeași poziție ca și valoarea căutată în prima coloană.
  • [Range_lookup] - Acest argument va oferi două opțiuni.
  • ADEVĂRAT - Potrivire aproximativă: - Argumentul poate fi introdus fie ca ADEVĂRAT sau numeric „1”, care returnează potrivirea aproximativă corespunzătoare coloanei de referință sau primei coloane. Mai mult, valorile din prima coloană a matricei de tabele trebuie să fie sortate în ordine crescătoare.
  • FALS - Potrivire exactă: - Aici argumentul care trebuie introdus poate fi FALS sau „0” numeric. Această opțiune va returna potrivirea exactă a valorii corespunzătoare care trebuie identificată din poziția din prima gamă de coloane. Dacă nu căutați valoarea din prima coloană, va apărea un mesaj de eroare „# N / A”.

# 2 - Formula meciului

Funcția de potrivire returnează poziția celulei valorii introduse pentru matricea de tabel dată.

Toate argumentele din sintaxă sunt obligatorii.

  • Lookup_value - Aici argumentul introdus poate fi fie referința la celulă a valorii, fie un șir de text cu ghilimele duble a căror poziție de celulă trebuie să fie trasă.
  • Lookup_array - Este necesar să se introducă gama matricei pentru tabel a cărei valoare sau conținut de celulă se dorește a fi identificat.
  • [tip de potrivire] - Acest argument oferă trei opțiuni, așa cum se explică mai jos.
  • „1-Less than” - Aici argumentul care trebuie introdus este „1” numeric, care va returna valoarea care este mai mică sau egală cu valoarea de căutare. Și, de asemenea, matricea de căutare trebuie să fie sortată în ordine crescătoare.
  • „0-potrivire exactă” - Aici argumentul care trebuie introdus ar trebui să fie numeric „0”. Această opțiune va returna poziția exactă a valorii de căutare potrivite. Cu toate acestea, matricea de căutare poate fi în orice ordine.
  • „-1-Mai mare decât” -  Argumentul care trebuie introdus ar trebui să fie numeric „-1”. A treia opțiune găsește cea mai mică valoare care este mai mare sau egală cu valoarea de căutare. Aici ordinea pentru matricea de căutare trebuie plasată în ordine descrescătoare.

# 3 - VLOOKUP cu Formula MATCH

= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])

Cum se folosește VLOOKUP cu Formula de potrivire în Excel?

Exemplul de mai jos vă va ajuta să înțelegeți funcționarea formulei vlookup și potrivire atunci când puneți împreună.

Puteți descărca acest VLookup cu Match Excel Template aici - VLookup with Match Excel Template

Luați în considerare tabelul de date de mai jos, care descrie specificațiile vehiculului dat de achiziționat.

Pentru a obține claritatea funcției combinate pentru vlookup și funcția de potrivire, să înțelegem cum funcționează formula individuală și apoi să ajungem la rezultatele potrivirii vlookup atunci când sunt puse împreună.

Pasul 1 - Să aplicăm formula vlookup la nivel individual pentru a ajunge la rezultat.

Ieșirea este prezentată mai jos:

Aici, valoarea de căutare se referă la $ B9, care este modelul „E”, iar matricea de căutare este dată ca intervalul tabelului de date cu valoarea absolută „$”, indexul coloanei se face referire la coloana „4”, care este numărul pentru coloana „Tastați” și căutarea intervalului este potrivită exact.

Astfel se aplică următoarea formulă pentru returnarea valorii pentru coloana „Combustibil”.

Ieșirea este prezentată mai jos:

Aici, valoarea de căutare cu șirul absolut „$” aplicat pentru valoarea de căutare și lookup_array ajută la fixarea celulei de referință chiar dacă formula este copiată într-o celulă diferită. În coloana „Combustibil”, trebuie să schimbăm indexul coloanei la „5” pe măsură ce se modifică valoarea de la care sunt necesare datele pentru a fi recuperate.

Pasul 2 -  Acum permiteți-ne să aplicăm formula Match pentru a prelua poziția pentru valoarea de căutare dată.

Ieșirea este prezentată mai jos:

După cum se poate vedea în captura de ecran de mai sus, aici încercăm să preluăm poziția coloanei din tabelul de tabele. În acest caz, numărul coloanei care trebuie trasă este denumit celula C8, care este coloana „Tip”, iar intervalul de căutare care trebuie căutat este dat ca intervalul de anteturi de coloană, iar tipului de potrivire i se dă o potrivire exactă ca „ 0 ”.

Astfel, tabelul de mai jos va da rezultatul dorit pentru pozițiile coloanei „Combustibil”.

Acum, coloana căutată este dată celula D8, iar indexul coloanei dorit este returnat la „5”.

Pasul 3 - Acum formula de potrivire va fi utilizată în cadrul funcției vlookup pentru a obține valoarea din poziția identificată a coloanei.

Ieșirea este prezentată mai jos:

În formula de mai sus, funcția de potrivire este plasată în locul parametrului index coloană al funcției vlookup. Aici funcția de potrivire va identifica celula de referință a valorii căutării „C8” și va returna numărul coloanei prin tabloul de tabel dat. Această poziție a coloanei va servi scopului ca intrare în argumentul indexului coloanei în funcția vlookup. Care, la rândul său, va ajuta vlookup să identifice valoarea care trebuie returnată din numărul de index al coloanei rezultat?

În mod similar, am aplicat vlookup cu formula de potrivire și pentru coloana „Combustibil”.

Ieșirea este prezentată mai jos:

Prin urmare, putem aplica această funcție de combinație și pentru alte coloane „Tip” și „Combustibil”.

Lucruri de amintit

  • VLOOKUP poate fi aplicat valorilor de căutare numai în partea sa cea mai importantă din stânga. Orice valoare prezentă care trebuie căutată în partea dreaptă a tabelului de date va returna valoarea de eroare „# N / A”.
  • Gama de table_array introdusă în cel de-al doilea argument trebuie să fie referința absolută a celulei „$”, aceasta va menține gama fixă ​​a tabelelor de tabel atunci când se aplică formula de căutare altor celule sau altfel celulele de referință pentru gama de tabele se vor deplasa la următoarea celulă referinţă.
  • Valoarea introdusă în valoarea de căutare nu trebuie să fie mai mică decât cea mai mică valoare din prima coloană a tabloului de tabele, altfel funcția va returna valoarea de eroare „# N / A”.
  • Înainte de a aplica o potrivire aproximativă „TRUE” sau „1” în ultimul argument, amintiți-vă întotdeauna să sortați matricea de tabele în ordine crescătoare.
  • Funcția de potrivire returnează doar poziția valorii în tabloul vlookup și nu returnează valoarea.
  • În cazul în care funcția de potrivire nu este în măsură să identifice poziția valorii de căutare în matricea de tabele, atunci formula returnează „# N / A” în valoarea de eroare.
  • Funcțiile Vlookup și Match nu disting majusculele și minusculele atunci când se potrivește valoarea de căutare cu valoarea textului care se potrivește în matricea de tabele.