Salt la conținutul principal
 

Peste 20 de exemple de CĂUTARE VOL pentru utilizatorii Excel începători și avansați

Autor: Xiaoyang Ultima modificare: 2023-11-30

Funcția CĂUTARE V este una dintre cele mai populare funcții din Excel. Acest tutorial va prezenta modul de utilizare a funcției CĂUTARE V în Excel cu zeci de exemple de bază și avansate pas cu pas.


Introducerea funcției VLOOKUP - Sintaxă și argumente

În Excel, funcția VLOOKUP este o funcție puternică pentru majoritatea utilizatorilor Excel, vă permite să căutați o valoare în partea stângă a intervalului de date și să returnați o valoare potrivită în același rând dintr-o coloană pe care ați specificat-o, după cum se arată în următoarea captură de ecran. .

Sintaxa funcției VLOOKUP:

=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

argumente:

Căutare_valor (obligatoriu): valoarea pe care doriți să o căutați. Poate fi o valoare (număr, dată sau text) sau o referință de celulă. Trebuie să fie în prima coloană a intervalului table_array. 

Table_array (obligatoriu): intervalul de date sau tabelul în care se află coloana cu valoarea de căutare și coloana cu valoarea rezultatului.

Col_index_num (obligatoriu): numărul coloanei care conține valorile returnate. Începe cu 1 din coloana cea mai din stânga din matricea tabelului.

Range_lookup (opțional): O valoare logică care determină dacă această funcție CĂUTARE V va returna o potrivire exactă sau o potrivire aproximativă.

  • Potrivire aproximativă – 1 / TRUE / omis (implicit): dacă nu se găsește o potrivire exactă, formula caută cea mai apropiată potrivire - cea mai mare valoare care este mai mică decât valoarea de căutare.
    Observa: În acest caz, trebuie să sortați coloana de căutare (coloana din stânga a intervalului de date) în ordine crescătoare, altfel va returna un rezultat greșit sau #N/A.
  • Potrivire exactă – 0/FALSE: Aceasta este utilizată pentru a căuta o valoare exact egală cu valoarea de căutare. Dacă nu se găsește o potrivire exactă, valoarea de eroare # N / A va fi returnată.

Note funcționale:

  • Funcția Vlookup caută doar o valoare de la stânga la dreapta.
  • Funcția Vlookup efectuează o căutare fără majuscule și minuscule.
  • Dacă există mai multe valori de potrivire bazate pe valoarea de căutare, numai prima potrivire va fi returnată utilizând funcția Vlookup.

Exemple de bază VLOOKUP

În această secțiune, vom vorbi despre câteva formule Vlookup pe care le-ați folosit frecvent.

2.1 Potrivire exactă și potrivire aproximativă CĂUTARE V

 2.1.1 Efectuați o potrivire exactă CĂUTARE V

În mod normal, dacă căutați o potrivire exactă cu funcția VLOOKUP, trebuie doar să utilizați FALSE ca ultim argument.

De exemplu, pentru a obține scorurile matematice corespunzătoare pe baza numerelor de identificare specifice, vă rugăm să procedați astfel:

Vă rugăm să copiați și să inserați formula de mai jos într-o celulă goală (aici, selectez G2) și apăsați Intrați cheie pentru a obține rezultatul:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Notă: În formula de mai sus, există patru argumente:

  • F2 este celula care conține valoarea C1005 pe care doriți să o căutați;
  • A2: D7 este matricea tabelului în care efectuați căutarea;
  • 3 este numărul coloanei din care este returnată valoarea potrivită; (Odată ce funcția identifică ID-ul - C1005, va merge la a treia coloană a matricei tabelului și va returna valorile din același rând cu cel al ID-ului - C1005. )
  • FALS se referă la potrivirea exactă.

Cum funcționează formula VLOOKUP?

În primul rând, caută ID-ul - C1005 în coloana cea mai din stânga a tabelului. Merge de sus în jos și găsește valoarea în celula A6.

De îndată ce găsește valoarea, merge la dreapta în a treia coloană și extrage valoarea din ea.

Deci, veți obține rezultatul așa cum se arată mai jos:

Notă: Dacă valoarea de căutare nu este găsită în coloana din stânga, returnează o eroare #N/A.
🤖 Kutools AI Aide: Revoluționați analiza datelor pe baza: Execuție inteligentă   |  Generați codul  |  Creați formule personalizate  |  Analizați datele și generați diagrame  |  Invocați funcțiile Kutools...
Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii  |   VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ...
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ...
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele   |  Afișați coloanele  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar   |  Manager registru de lucru și foi  |  Biblioteca de resurse   |  Data Picker  |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (prin bold/italic...) ...
Top 15 set de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   Multe altele...

Kutools pentru Excel se mândrește cu peste 300 de caracteristici, Asigurați-vă că ceea ce aveți nevoie este la doar un clic distanță...

 
 2.1.2 Efectuați o potrivire aproximativă CĂUTARE V

Potrivirea aproximativă este utilă pentru căutarea valorilor între intervalele de date. Dacă nu se găsește potrivirea exactă, VLOOKUP aproximativă va returna cea mai mare valoare care este mai mică decât valoarea de căutare.

De exemplu, dacă aveți următorul interval de date și comenzile specificate nu sunt în coloana Comenzi, cum să obțineți cea mai apropiată reducere în coloana B?

Pasul 1: Aplicați formula VLOOKUP și completați-o în alte celule

Copiați și inserați următoarea formulă într-o celulă în care doriți să puneți rezultatul, apoi trageți mânerul de umplere în jos pentru a aplica această formulă altor celule.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Rezultat:

Acum, veți obține potrivirile aproximative pe baza valorilor date, vedeți captura de ecran:

note:

  • În formula de mai sus:
    • D2 este valoarea pe care doriți să-i returnați informațiile relative;
    • A2: B9 este intervalul de date;
    • 2 indică numărul coloanei la care este returnată valoarea potrivită;
    • TRUE se referă la potrivirea aproximativă.
  • Potrivirea aproximativă va returna cea mai mare valoare care este mai mică decât valoarea de căutare specifică dacă nu se găsește o potrivire exactă.
  • Pentru a utiliza funcția CĂUTARE V pentru a obține o valoare de potrivire aproximativă, trebuie să sortați coloana din stânga a intervalului de date în ordine crescătoare, altfel va returna un rezultat greșit.

2.2 Efectuați o CĂUTARE VIT sensibilă la majuscule și minuscule în Excel

În mod implicit, funcția VLOOKUP efectuează o căutare care nu ține seama de majuscule, ceea ce înseamnă că tratează caracterele litere mici și majuscule ca fiind identice. Uneori, este posibil să fie nevoie să efectuați o căutare care ține seama de majuscule și minuscule în Excel, este posibil ca funcția normală CĂUTARE V să nu o rezolve. În acest caz, puteți utiliza funcții alternative precum INDEX și MATCH cu funcția EXACT, sau funcțiile LOOKUP și EXACT.

De exemplu, am următorul interval de date, care coloană ID conține șir de text cu majuscule sau minuscule, acum, vreau să returnez scorul corespunzător de matematică al numărului de identificare dat.

Pasul 1: Aplicați orice formulă și completați-o în alte celule

Vă rugăm să copiați și să inserați oricare dintre formulele de mai jos într-o celulă goală de unde doriți să obțineți rezultatul. Apoi, selectați celula formulei, trageți mânerul de umplere în jos până la celulele în care doriți să completați această formulă.

Formula 1: După ce lipiți formula, apăsați Ctrl + Shift + Enter chei.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formula 2: După ce lipiți formula, apăsați Intrați cheie.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Rezultat:

Apoi veți obține rezultatele corecte de care aveți nevoie. Vedeți captura de ecran:

note:

  • În formula de mai sus:
    • A2: A10 este coloana care conține valorile specifice în care doriți să căutați;
    • F2 este valoarea de căutare;
    • C2: C10 este coloana de unde va fi returnat rezultatul.
  • Dacă sunt găsite mai multe potriviri, această formulă va returna întotdeauna ultima potrivire.

2.3 Valori VLOOKUP de la dreapta la stânga în Excel

Funcția VLOOKUP caută întotdeauna o valoare în coloana cea mai din stânga a unui interval de date și returnează valoarea corespunzătoare dintr-o coloană la dreapta. Dacă doriți să efectuați o CĂUTARE inversă, ceea ce înseamnă să căutați o anumită valoare în coloana din dreapta și să returnați valoarea corespunzătoare în coloana din stânga, așa cum se arată mai jos:

Faceți clic pentru a afla detaliile pas cu pas despre această sarcină ...


2.4 CĂUTARE V a doua, a n-a sau ultima valoare potrivită în Excel

În mod normal, dacă se găsesc mai multe valori care se potrivesc atunci când se utilizează funcția Vlookup, va fi returnată doar prima înregistrare potrivită. În această secțiune, voi vorbi despre cum să obțineți a doua, a n-a sau ultima valoare de potrivire dintr-un interval de date.

 2.4.1 CĂUTARE V și returnează a doua sau a n-a valoare de potrivire

Să presupunem că aveți o listă de nume în coloana A, cursul de formare pe care l-au achiziționat în coloana B. Acum, căutați să găsiți al 2-lea sau al al-lea curs de formare cumpărat de clientul dat. Vedeți captura de ecran:

Aici, este posibil ca funcția VLOOKUP să nu rezolve această sarcină în mod direct. Dar, puteți utiliza funcția INDEX ca alternativă.

Pasul 1: Aplicați și completați formula în alte celule

De exemplu, pentru a obține a doua valoare de potrivire pe baza criteriilor date, vă rugăm să aplicați următoarea formulă într-o celulă goală și apăsați Ctrl + Shift + Enter cheile împreună pentru a obține primul rezultat. Apoi, selectați celula formulei, trageți mânerul de umplere în jos până în celulele în care doriți să completați această formulă.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Rezultat:

Acum, toate cele doua valori potrivite pe baza numelor date au fost afișate simultan.

Notă: În formula de mai sus:

  • A2: A14 este intervalul cu toate valorile pentru căutare;
  • B2: B14 este intervalul de valori de potrivire de la care doriți să reveniți;
  • E2 este valoarea de căutare;
  • 2 indică a doua valoare potrivită pe care doriți să o obțineți, pentru a returna a treia valoare potrivită, trebuie doar să o schimbați la 3.
 2.4.2 VLOOKUP și returnează ultima valoare potrivită

Dacă doriți să vizualizați și să returnați ultima valoare potrivită, așa cum se arată în imaginea de mai jos, aceasta CĂUTARE V și returnați ultima valoare potrivită tutorialul vă poate ajuta să obțineți ultima valoare potrivită în detalii.


2.5 VLOOKUP potrivirea valorilor între două valori sau date date

Uneori, este posibil să doriți să căutați valori între două valori sau date și să returnați rezultatele corespunzătoare, așa cum se arată în captura de ecran de mai jos. În acest caz, puteți utiliza funcția LOOKUP în loc de funcția VLOOKUP cu un tabel sortat.

 2.5.1 CĂUTARE V potrivirea valorilor între două valori date sau date cu formulă

Pasul 1: Aranjați datele și aplicați următoarea formulă

Tabelul original ar trebui să fie un interval de date sortat. Apoi, copiați sau introduceți următoarea formulă într-o celulă goală. Apoi, trageți mânerul de umplere pentru a completa această formulă în alte celule de care aveți nevoie.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Rezultat:

Și acum, veți obține toate înregistrările potrivite pe baza valorii date, vedeți captura de ecran:

note:

  • În formula de mai sus:
    • A2: A6 este intervalul de valori mai mici;
    • B2: B6 este intervalul de numere mai mari;
    • E2 este valoarea de căutare pe care doriți să o obțineți valoarea corespunzătoare;
    • C2: C6 este coloana din care doriți să returnați o valoare corespunzătoare.
  • Această formulă poate fi folosită și pentru extragerea valorilor potrivite între două date, după cum se arată mai jos:
 2.5.2 VLOOKUP potrivirea valorilor între două valori date sau date cu o caracteristică la îndemână

Dacă vă este greu să vă amintiți și să înțelegeți formula de mai sus, aici, voi introduce un instrument ușor - Kutools pentru Excel, Cu său CĂUTARE între două valori funcția, puteți returna articolul corespunzător pe baza valorii sau datei specifice dintre două valori sau date cu ușurință.

  1. Clic Kutools > Super CAUTARE > CĂUTARE între două valori pentru a activa această caracteristică.
  2. Apoi specificați operațiunile din caseta de dialog pe baza datelor dvs.
notițe: Pentru a aplica această caracteristică, ar trebui să descărcați Kutools pentru Excel cu o perioadă de încercare gratuită de 30 de zile in primul rand.


2.6 Utilizarea wildcards pentru potriviri parțiale în funcția VLOOKUP

În Excel, metacaracterele pot fi utilizate în cadrul funcției CĂUTARE V, care vă permite să efectuați o potrivire parțială a unei valori de căutare. De exemplu, puteți utiliza VLOOKUP pentru a returna valoarea potrivită dintr-un tabel pe baza unei părți a unei valori de căutare.

Presupunând că am o serie de date așa cum se arată în imaginea de mai jos, acum vreau să extrag scorul pe baza prenumelui (nu a numelui complet). Cum ar putea rezolva această sarcină în Excel?

Pasul 1: Aplicați și completați formula în alte celule

Copiați sau introduceți următoarea formulă într-o celulă goală, apoi trageți mânerul de umplere pentru a completa această formulă în alte celule de care aveți nevoie:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Rezultat:

Și toate scorurile potrivite au fost returnate așa cum se arată mai jos:

Notă: În formula de mai sus:

  • E2 și „*” este criteriul pentru matematica parțială. Aceasta înseamnă că căutați orice valoare care începe cu valoarea din celula E2. (Caracterul „*” indică orice caracter sau orice caracter)
  • A2: C11 este intervalul de date în care doriți să căutați valoarea potrivită;
  • 3 înseamnă a returna valoarea potrivită din a 3-a coloană a intervalului de date;
  • Fals indică matematica exactă. (Când folosiți caractere joker, trebuie să setați ultimul argument din funcție ca FALSE sau 0 pentru a activa modul de potrivire exactă în funcția CĂUTARE V.)
sfaturi:
  • Pentru a găsi și a returna valorile care se potrivesc care se termină cu o anumită valoare, ar trebui să puneți wildcardul „*” în fața valorii. Vă rugăm să aplicați această formulă:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

  • Pentru a căuta și a returna valoarea potrivită pe baza unei părți a șirului de text, indiferent dacă textul specificat este la bebinning, la sfârșitul sau la mijlocul șirului de text, trebuie doar să includeți referința celulei sau textul cu două asteriscuri (*) de ambele părți. Vă rog să faceți cu această formulă
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)


2.7 Valori VLOOKUP dintr-o altă foaie de lucru

De obicei, este posibil să trebuiască să lucrați cu mai mult de o foaie de lucru, funcția VLOOKUP poate fi utilizată pentru a căuta date dintr-o altă foaie la fel ca pe o singură foaie de lucru.

De exemplu, aveți două foi de lucru, așa cum se arată în imaginea de mai jos, pentru a căuta și a returna datele corespunzătoare din foaia de lucru pe care ați specificat-o, vă rugăm să parcurgeți pașii următori:

Pasul 1: Aplicați și completați formula în alte celule

Introduceți sau copiați formula de mai jos într-o celulă goală de unde doriți să obțineți elementele potrivite. Apoi, trageți mânerul de umplere în jos până la celulele pe care doriți să le aplicați această formulă.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Rezultat:

Veți obține rezultatele corespunzătoare după cum aveți nevoie, vedeți captura de ecran:

Notă: În formula de mai sus:

  • A2 reprezintă valoarea de căutare;
  • „Fișă tehnică”!A2:C15 indică căutarea valorilor din intervalul A2:C15 pe foaia de lucru numită Fișă de date; (Dacă numele foii conține spații sau caractere de punctuație, ar trebui să includeți numele foii între ghilimele simple, în caz contrar, puteți utiliza direct numele foii ca =CĂUTAREV(A2,Foaie de date!$A$2:$C$15,3,0) ).
  • 3 este numărul coloanei care conține datele potrivite de la care doriți să reveniți;
  • 0 înseamnă a realiza o potrivire exactă.

2.8 Valori VLOOKUP dintr-un alt registru de lucru

Această secțiune va vorbi despre căutare și va returna valorile potrivite dintr-un registru de lucru diferit, utilizând funcția CĂUTARE V.

De exemplu, să presupunem că aveți două registre de lucru. Primul registru de lucru conține o listă de produse și costurile acestora. În al doilea registru de lucru, doriți să extrageți costul corespunzător pentru fiecare articol de produs, așa cum se arată mai jos.

Pasul 1: Aplicați și completați formula

Deschideți ambele registre de lucru pe care doriți să le utilizați, apoi aplicați următoarea formulă într-o celulă în care doriți să puneți rezultatul în al doilea registru de lucru. Apoi, trageți și copiați această formulă în alte celule de care aveți nevoie

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Rezultat:

note:

  • În formula de mai sus:
    • B2 reprezintă valoarea de căutare;
    • „[Lista de produse.xlsx]Sheet1”!A2:B6 indică căutarea din intervalul A2:B6 pe foaia numită Sheet1 din registrul de lucru Lista de produse; (Referința la registrul de lucru este inclusă între paranteze pătrate, iar întregul registru de lucru + foaia sunt incluse între ghilimele simple.)
    • 2 este numărul coloanei care conține datele potrivite de la care doriți să reveniți;
    • 0 indică returnarea unei potriviri exacte.
  • Dacă registrul de lucru de căutare este închis, calea completă a fișierului pentru registrul de lucru de căutare va fi afișată în formulă, după cum se arată în următoarea captură de ecran:

2.9 Returnați text necompletat sau specific în loc de eroare 0 sau #N/A

De obicei, când utilizați funcția CĂUTARE V pentru a returna o valoare corespunzătoare, dacă celula potrivită este goală, va returna 0. Și dacă valoarea potrivită nu este găsită, veți obține o valoare de eroare de #N/A așa cum se arată în captură de ecran de mai jos. Dacă doriți să afișați o celulă goală sau o anumită valoare în loc de 0 sau #N/A, aceasta CĂUTARE V Pentru a returna o valoare necompletă sau specifică în loc de 0 sau N/A tutorialul îți poate face o favoare.


Exemple avansate de VLOOKUP

3.1 Căutare bidirecțională (CĂUTARE V în rând și coloană)

Uneori, poate fi necesar să efectuați o căutare bidimensională, ceea ce înseamnă să căutați o valoare atât în ​​rând, cât și în coloană în același timp. De exemplu, dacă aveți următorul interval de date și poate fi necesar să obțineți valoarea pentru un anumit produs într-un trimestru specificat. Această secțiune va introduce o formulă pentru gestionarea acestei sarcini în Excel.

În Excel, puteți utiliza o combinație de funcții CĂUTARE V și POTRIVIRE pentru a efectua o căutare bidirecțională.

Aplicați următoarea formulă într-o celulă goală, apoi apăsați Intrați cheie pentru a obține rezultatul.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

Notă: În formula de mai sus:

  • G2 este valoarea de căutare din coloana pe care doriți să obțineți valoarea corespunzătoare pe baza;
  • A2: E7 este tabelul de date din care veți căuta;
  • H1 este valoarea de căutare din rândul pe care doriți să obțineți valoarea corespunzătoare pe baza;
  • A2: E2 este celulele antetelor coloanei;
  • FALS indică obținerea unei potriviri exacte.

3.2 Valoare de potrivire CĂUTARE V bazată pe două sau mai multe criterii

Vă este ușor să căutați valoarea de potrivire pe baza unui criteriu, dar dacă aveți două sau mai multe criterii, ce puteți face?

 3.2.1 Valoare de potrivire CĂUTARE V bazată pe două sau mai multe criterii cu formule

În acest caz, funcțiile LOOKUP sau MATCH și INDEX din Excel vă pot ajuta să rezolvați rapid și ușor această lucrare.

De exemplu, am tabelul de date de mai jos, pentru a returna prețul potrivit pe baza produsului și mărimii specifice, următoarele formule vă pot ajuta.

Pasul 1: Aplicați orice formulă

Formula 1: După ce lipiți formula, apăsați Intrați cheie.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formula 2: După ce lipiți formula, apăsați Ctrl + Shift + Enter chei.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Rezultat:

note:

  • În formulele de mai sus:
    • A2: A12 = G1 mijloace de căutare a criteriilor lui G1 în intervalul A2:A12;
    • B2: B12 = G2 mijloace de căutare a criteriilor lui G2 în intervalul B2:B12;
    • D2: D12 is intervalul din care doriți să returnați valoarea corespunzătoare.
  • Dacă aveți mai mult de două criterii, trebuie doar să uniți celelalte criterii în formulă, cum ar fi:
    =LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
    =INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
 3.2.2 Valoarea de potrivire a CĂUTARE VL pe baza a două sau mai multe criterii cu o funcție inteligentă

Poate fi o provocare să vă amintiți formulele complexe de mai sus care trebuie aplicate în mod repetat, ceea ce vă poate încetini eficiența muncii. In orice caz, Kutools pentru Excel oferă Căutare în mai multe condiții caracteristică care vă permite să returnați rezultatul corespunzător pe baza uneia sau mai multor condiții cu doar câteva clicuri.

  1. Clic Kutools > Super CAUTARE > Căutare în mai multe condiții pentru a activa această caracteristică.
  2. Apoi specificați operațiunile din caseta de dialog pe baza datelor dvs.
notițe: Pentru a aplica această caracteristică, ar trebui să descărcați Kutools pentru Excel cu o perioadă de încercare gratuită de 30 de zile in primul rand.


3.3 VLOOKUP pentru a returna mai multe valori cu unul sau mai multe criterii

În Excel, funcția VLOOKUP caută o valoare și returnează prima valoare potrivită numai dacă sunt găsite mai multe valori corespunzătoare. Uneori, poate doriți să returnați toate valorile corespunzătoare într-un rând, într-o coloană sau într-o singură celulă. Această secțiune va vorbi despre cum să returnați mai multe valori de potrivire cu una sau mai multe condiții într-un registru de lucru.

 3.3.1 VLOOKUP toate valorile care se potrivesc bazate pe una sau mai multe condiții pe orizontală

Presupunând că aveți un tabel de date care conține țara, orașul și numele în intervalul A1:C14, iar acum, doriți să returnați toate numele orizontal care provin din „SUA”, așa cum se arată mai jos. Pentru a rezolva această sarcină, vă rog faceți clic aici pentru a obține rezultatul pas cu pas.

 3.3.2 VLOOKUP toate valorile care se potrivesc pe una sau mai multe condiții pe verticală

Dacă trebuie să căutați și să returnați toate valorile care se potrivesc pe verticală, pe baza unor criterii specifice, așa cum se arată în captura de ecran de mai jos, dați clic aici pentru a obține soluția în detalii.

 3.3.3 VLOOKUP toate valorile care se potrivesc pe baza uneia sau mai multor condiții într-o singură celulă

Dacă doriți să căutați și să returnați mai multe valori potrivite într-o singură celulă cu separatorul specificat, noua funcție a TEXTJOIN vă poate ajuta să rezolvați acest lucru rapid și ușor.

note:


3.4 CĂUTARE V pentru a returna întregul rând al unei celule potrivite

În această secțiune, voi vorbi despre cum să preluați întregul rând al unei valori potrivite utilizând funcția CĂUTARE V.

Pasul 1: Aplicați și completați următoarea formulă

Vă rugăm să copiați sau să tastați formula de mai jos într-o celulă goală în care doriți să scoateți rezultatul și apăsați Intrați tasta pentru a obține prima valoare. Apoi, trageți celula formulei la dreapta până când sunt afișate datele întregului rând.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Rezultat:

Acum, puteți vedea că toate datele rândului sunt returnate. Vedeți captura de ecran:
funcția de căutare documentare 50 1

Notă: în formula de mai sus:

  • F2 este valoarea de căutare pe care doriți să returnați întregul rând pe baza;
  • A1: D12 este intervalul de date din care doriți să căutați valoarea de căutare;
  • A1 indică primul număr de coloană din intervalul de date;
  • FALS indică căutarea exactă.

Sfat:

  • Dacă sunt găsite mai multe rânduri pe baza valorii potrivite, pentru a returna toate rândurile corespunzătoare, aplicați formula de mai jos, apoi apăsați Ctrl + Shift + Enter cheile împreună pentru a obține primul rezultat. Apoi trageți mânerul de umplere la dreapta. Și apoi, continuați să trageți mânerul de umplere în jos peste celule pentru a obține toate rândurile care se potrivesc. Vezi demo-ul de mai jos:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    funcția de căutare documentare 51 2

3.5 VLOOKUP imbricat în Excel

Uneori, poate fi necesar să căutați valori care sunt interconectate în mai multe tabele. În acest caz, puteți imbrica mai multe funcții VLOOKUP împreună pentru a obține valoarea finală.

De exemplu, am o foaie de lucru care conține două tabele separate. Primul tabel listează toate numele produselor împreună cu vânzătorul corespunzător. Al doilea tabel prezintă vânzările totale ale fiecărui vânzător. Acum, dacă doriți să găsiți vânzările fiecărui produs, așa cum se arată în următoarea captură de ecran, puteți imbrica funcția VLOOKUP pentru a îndeplini această sarcină.
funcția de căutare documentare 53 1

Formula generică pentru funcția VLOOKUP imbricată este:

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

Notă:

  • lookup_value este valoarea pe care o cauți;
  • Table_array1, Table_array2 sunt tabelele în care există valoarea de căutare și valoarea returnată;
  • col_index_num1 indică numărul coloanei din primul tabel pentru găsirea datelor comune intermediare;
  • col_index_num2 indică numărul coloanei din al doilea tabel pentru care doriți să returnați valoarea potrivită;
  • 0 este folosit pentru o potrivire exactă.

Pasul 1: Aplicați și completați următoarea formulă

Aplicați următoarea formulă într-o celulă goală, apoi trageți mânerul de umplere în jos până la celulele pe care doriți să le aplicați această formulă.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Rezultat:

Acum, veți obține rezultatul așa cum se arată în următoarea captură de ecran:

Notă: în formula de mai sus:

  • G3 conține valoarea pe care o cauți;
  • A3: B7, D3: E7 sunt intervalele de tabel în care există valoarea de căutare și valoarea returnată;
  • 2 este numărul coloanei din intervalul din care returnează valoarea potrivită.
  • 0 indică matematică exactă CĂUTARE V.

3.6 Verificați dacă valoarea există pe baza datelor unei liste dintr-o altă coloană

Funcția VLOOKUP vă poate ajuta, de asemenea, să verificați dacă există valori pe baza listei de date dintr-o altă coloană. De exemplu, dacă doriți să căutați numele în coloana C și să returnați doar Da sau Nu dacă numele este găsit sau nu în coloana A, așa cum se arată mai jos.
funcția de căutare documentare 56 1

Pasul 1: Aplicați și completați următoarea formulă

Aplicați următoarea formulă într-o celulă goală, apoi trageți mânerul de umplere în jos până la celulele în care doriți să umpleți această formulă.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Rezultat:

Și veți obține rezultatul după cum aveți nevoie, vedeți captura de ecran:

Notă: în formula de mai sus:

  • C2 este valoarea de căutare pe care doriți să o verificați;
  • A2: A10 este lista intervalului de unde se verifică dacă valorile de căutare vor fi găsite sau nu;
  • FALS indică obținerea unei potriviri exacte.

3.7 CĂUTARE V și însumați toate valorile potrivite în rânduri sau coloane

Când lucrați cu date numerice, poate fi necesar să extrageți valori potrivite dintr-un tabel și să însumați numerele în mai multe coloane sau rânduri. Această secțiune va introduce câteva formule care vă pot ajuta să îndepliniți această sarcină.

 3.7.1 CĂUTARE V și însumați toate valorile potrivite într-un rând sau pe mai multe rânduri

Să presupunem că aveți o listă de produse cu vânzări de câteva luni, așa cum se arată în următoarea captură de ecran. Acum, trebuie să însumați toate comenzile din toate lunile pe baza produselor date.

Pasul 1: Aplicați și completați următoarea formulă

Vă rugăm să copiați sau să introduceți următoarea formulă într-o celulă goală, apoi apăsați Ctrl + Shift + Enter cheile împreună pentru a obține primul rezultat. Apoi, trageți mânerul de umplere în jos pentru a copia această formulă în alte celule de care aveți nevoie.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Rezultat:

Toate valorile dintr-un rând ale primei valori de potrivire au fost însumate, vezi captura de ecran:

Notă: în formula de mai sus:

  • H2 este celula care conține valoarea pe care o căutați;
  • A2: F9 este intervalul de date (fără antetele de coloană) care includ valoarea de căutare și valorile potrivite;
  • 2,3,4,5,6 {} sunt numerele coloanelor folosite pentru a calcula totalul intervalului;
  • FALS indică o potrivire exactă.

Sfat: Dacă doriți să însumați toate potrivirile pe mai multe rânduri, vă rugăm să utilizați următoarea formulă:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
 3.7.2 CĂUTARE V și însumați toate valorile potrivite într-o coloană sau mai multe coloane

Dacă doriți să însumați valoarea totală pentru anumite luni, așa cum se arată în captura de ecran de mai jos. Este posibil ca funcția normală de CĂUTARE V să nu vă ajute, aici ar trebui să aplicați împreună funcțiile SUM, INDEX și MATCH pentru a crea o formulă.

Pasul 1: Aplicați următoarea formulă

Aplicați formula de mai jos într-o celulă goală, apoi trageți mânerul de umplere în jos pentru a copia această formulă în alte celule.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Rezultat:

Acum, primele valori potrivite pe baza unei anumite luni dintr-o coloană au fost însumate împreună, vezi captura de ecran:

Notă: în formula de mai sus:

  • H2 este celula care conține valoarea pe care o căutați;
  • B1: F1 este anteturile de coloană care conțin valoarea de căutare;
  • B2: F9 este intervalul de date care conține valorile numerice pe care doriți să le însumați.

Sfat: Pentru a CĂUTARE V și a însumați toate valorile potrivite în mai multe coloane, ar trebui să utilizați următoarea formulă:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
 3.7.3 CĂUTARE V și însumați primele valori potrivite sau toate valorile potrivite cu o funcție puternică

Poate că formulele de mai sus vă sunt greu de reținut, în acest caz, vă voi recomanda o caracteristică puternică - Căutare și sumă of Kutools pentru Excel, cu această caracteristică, puteți să căutați și să însumați prima potrivire sau toate valorile care se potrivesc în rânduri sau coloane cât mai ușor posibil.

  1. Clic Kutools > Super CAUTARE > CĂUTARE și Sumă pentru a activa această caracteristică.
  2. Apoi specificați operațiunile din caseta de dialog în funcție de nevoile dvs.
notițe: Pentru a aplica această caracteristică, ar trebui să descărcați Kutools pentru Excel cu o perioadă de încercare gratuită de 30 de zile in primul rand.
 3.7.4 CĂUTARE V și însumați toate valorile potrivite atât în ​​rânduri, cât și în coloane

Dacă doriți să însumați valorile atunci când trebuie să potriviți atât coloana, cât și rândul, de exemplu, pentru a obține valoarea totală a produsului Pulover în luna Mar, după cum se arată în imaginea de mai jos.

Aici, puteți utiliza funcția SUMPRODCT pentru a îndeplini această sarcină.

Aplicați următoarea formulă într-o celulă și apoi apăsați Intrați cheie pentru a obține rezultatul, vezi captura de ecran:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

Notă: În formula de mai sus:

  • B2: F9 este intervalul de date care conține valorile numerice pe care doriți să le însumați;
  • B1: F1 este anteturile de coloană care conțin valoarea de căutare pe care doriți să o însumați;
  • I2 este valoarea de căutare din anteturile de coloană pe care le căutați;
  • A2: A9 este antetele rândurilor care conțin valoarea de căutare pe care doriți să o însumați;
  • H2 este valoarea de căutare din anteturile rândurilor pe care le căutați.

3.8 VLOOKUP pentru a îmbina două tabele pe baza coloanelor cheie

În munca de zi cu zi, atunci când analizați datele, poate fi necesar să aduneți toate informațiile necesare într-un singur tabel bazat pe una sau mai multe coloane cheie. Pentru a îndeplini această sarcină, puteți utiliza funcțiile INDEX și MATCH în loc de funcția CĂUTARE V.

 3.8.1 CĂUTARE V pentru a îmbina două tabele bazate pe o singură coloană cheie

De exemplu, aveți două tabele, primul tabel care conține datele despre produse și nume, iar al doilea tabel conține datele despre produse și comenzi, acum doriți să combinați aceste două tabele prin potrivirea coloanei comune de produse într-un singur tabel.

Pasul 1: Aplicați și completați următoarea formulă

Vă rugăm să aplicați următoarea formulă într-o celulă goală. Apoi, trageți mânerul de umplere în jos până la celulele pe care doriți să le aplicați această formulă

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Rezultat:

Acum, veți obține un tabel îmbinat cu coloana de ordine care se alătură primului tabel pe baza datelor cheie ale coloanei.

Notă: În formula de mai sus:

  • A2 este valoarea de căutare pe care o căutați;
  • F2: F8 este intervalul de date pentru care doriți să returnați valorile potrivite;
  • E2: E8 este intervalul de căutare care conține valoarea de căutare.
 3.8.2 VLOOKUP pentru a îmbina două tabele bazate pe mai multe coloane cheie

Dacă cele două tabele la care doriți să vă alăturați au mai multe coloane cheie, pentru a îmbina tabelele pe baza acestor coloane comune, urmați pașii de mai jos.

Formula generică este:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

Notă:

  • tabel_căutare este intervalul de date care conține datele de căutare și înregistrările care se potrivesc;
  • valoare_căutare1 este primul criteriu pe care îl cauți;
  • interval_căutare1 lista de date conține primele criterii;
  • valoare_căutare2 este al doilea criteriu pe care îl cauți;
  • interval_căutare2 este lista de date conține al doilea criteriu;
  • return_column_number indică numărul coloanei din tabelul de căutare pentru care doriți să returnați valoarea potrivită.

Pasul 1: Aplicați următoarea formulă

Aplicați formula de mai jos într-o celulă goală în care doriți să puneți rezultatul, apoi apăsați Ctrl + Shift + Enter tastele împreună pentru a obține prima valoare potrivită, consultați captura de ecran:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Pasul 2: Completați formula în alte celule

Apoi, selectați prima celulă de formulă și trageți mânerul de umplere pentru a copia această formulă în alte celule după cum aveți nevoie:

sfaturi: În Excel 2016 sau versiuni ulterioare, puteți utiliza și Power Query caracteristică pentru a îmbina două sau mai multe tabele într-unul bazat pe coloane cheie. Vă rugăm să faceți clic pentru a afla detaliile pas cu pas.

3.9 VLOOKUP potrivirea valorilor în mai multe foi de lucru

Ați avut vreodată nevoie să efectuați o CĂUTARE V pe mai multe foi de lucru în Excel? De exemplu, dacă aveți trei foi de lucru cu intervale de date și doriți să preluați anumite valori pe baza criteriilor din aceste foi, puteți urma tutorialul pas cu pas Valori CĂUTARE V din mai multe foi de lucru pentru a îndeplini această sarcină.


Valorile potrivite VLOOKUP păstrează formatarea celulei

La căutarea valorilor potrivite, formatarea originală a celulei, cum ar fi culoarea fontului, culoarea fundalului, formatul datelor etc., nu va fi păstrată. Pentru a păstra formatarea celulei sau a datelor, această secțiune va introduce câteva trucuri pentru rezolvarea joburilor.

4.1 VLOOKUP potrivește valoarea și păstrează culoarea celulei, formatarea fontului

După cum știm cu toții, funcția normală VLOOKUP poate prelua doar valoarea potrivită dintr-un alt interval de date. Cu toate acestea, pot exista situații în care doriți să obțineți valoarea corespunzătoare împreună cu formatarea celulei, cum ar fi culoarea de umplere, culoarea fontului și stilul fontului. În această secțiune, vom discuta despre cum să extragem valorile potrivite, păstrând în același timp formatarea sursă în Excel.

Efectuați următorii pași pentru a căuta și a returna valoarea corespunzătoare împreună cu formatarea celulei:

Pasul 1: Copiați codul 1 în modulul de cod al foii

  1. În foaia de lucru conține datele pe care doriți să le căutați VLOOKUP, faceți clic dreapta pe fila foii și selectați Afișați codul din meniul contextual. Vedeți captura de ecran:
  2. În deschise Microsoft Visual Basic pentru aplicații fereastra, copiați codul VBA de mai jos în fereastra Cod.
  3. Cod VBA 1: VLOOKUP pentru a obține formatarea celulei împreună cu valoarea de căutare
  4. Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
        Dim I As Long
        Dim xKeys As Long
        Dim xDicStr As String
        On Error Resume Next
        Application.ScreenUpdating = False
        xKeys = UBound(xDic.Keys)
        If xKeys >= 0 Then
            For I = 0 To UBound(xDic.Keys)
                xDicStr = xDic.Items(I)
                If xDicStr <> "" Then
                    Range(xDic.Keys(I)).Interior.Color = _
                    Range(xDic.Items(I)).Interior.Color
                    Range(xDic.Keys(I)).Font.FontStyle = _
                    Range(xDic.Items(I)).Font.FontStyle
                    Range(xDic.Keys(I)).Font.Size = _
                    Range(xDic.Items(I)).Font.Size
                    Range(xDic.Keys(I)).Font.Color = _
                    Range(xDic.Items(I)).Font.Color
                    Range(xDic.Keys(I)).Font.Name = _
                    Range(xDic.Items(I)).Font.Name
                    Range(xDic.Keys(I)).Font.Underline = _
                    Range(xDic.Items(I)).Font.Underline
                Else
                    Range(xDic.Keys(I)).Interior.Color = xlNone
                End If
            Next
            Set xDic = Nothing
        End If
        Application.ScreenUpdating = True
    End Sub
    

Pasul 2: Copiați codul 2 în fereastra Modulului

  1. Cu toate acestea, în Microsoft Visual Basic pentru aplicații fereastră, faceți clic pe Insera > Module, apoi copiați codul VBA 2 de mai jos în fereastra Module.
  2. Cod VBA 2: VLOOKUP pentru a obține formatarea celulei împreună cu valoarea de căutare
  3. Public xDic As New Dictionary
    Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
        Dim xFindCell As Range
        On Error Resume Next
        Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
        If xFindCell Is Nothing Then
            LookupKeepFormat = ""
            xDic.Add Application.Caller.Address, ""
        Else
            LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
            xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
        End If
    End Function
    

Pasul 3: Selectați opțiunea pentru VBAproject

  1. După introducerea codurilor de mai sus, apoi faceți clic Instrumente > Referinte în Microsoft Visual Basic pentru aplicații fereastră. Apoi verificați Runtime Microsoft Scripting caseta de selectare din Referințe - VBAProject căsuță de dialog. Vedeți capturi de ecran:
  2. Apoi apasa OK pentru a închide caseta de dialog, apoi salvați și închideți fereastra de cod.

Pasul 4: Introduceți formula pentru obținerea rezultatului

  1. Acum, reveniți la foaia de lucru, aplicați următoarea formulă. Apoi, trageți mânerul de umplere în jos pentru a obține toate rezultatele împreună cu formatarea acestora. Vedeți captura de ecran:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

Notă: în formula de mai sus:

  • E2 este valoarea pe care o vei căuta;
  • A1: C10 este intervalul de masă;
  • 3 este numărul coloanei tabelului din care doriți să preluați valoarea potrivită.

4.2 Păstrați formatul datei dintr-o valoare returnată CĂUTARE V

Când utilizați funcția CĂUTARE V pentru a căuta și a returna o valoare cu format de dată, rezultatul returnat se poate afișa ca număr. Pentru a păstra formatul datei în rezultatul returnat, ar trebui să includeți funcția CĂUTARE V în funcția TEXT.

Pasul 1: Aplicați și completați următoarea formulă

Vă rugăm să aplicați formula de mai jos într-o celulă goală. Apoi, trageți mânerul de umplere pentru a copia această formulă în alte celule.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Rezultat:

Toate datele potrivite au fost returnate după cum se arată în captura de ecran de mai jos:

Notă: În formula de mai sus:

  • E2 este valoarea de căutare;
  • A2: C9 este intervalul de căutare;
  • 3 este numărul coloanei pentru care doriți să fie returnată valoarea;
  • FALS indică obținerea unei potriviri exacte;
  • mm / zz / aaaa este formatul de dată pe care doriți să îl păstrați.

4.3 Întoarceți comentariul pentru celulă din VLOOKUP

Ați avut vreodată nevoie să regăsiți atât datele celulelor care se potrivesc, cât și comentariul asociat folosind CUȚIUNE V în Excel, așa cum se arată în următoarea captură de ecran? Dacă da, funcția definită de utilizator furnizată mai jos vă poate ajuta să îndepliniți această sarcină.

Pasul 1: Copiați codul într-un Modul

  1. Țineți apăsată tasta ALT + F11 tastele pentru a deschide Microsoft Visual Basic pentru aplicații fereastră.
  2. Clic Insera > Module, apoi copiați și lipiți următorul cod în fereastra modulului.
    Cod VBA: Vlookup și returnează valoarea de potrivire cu comentariul celulei:
    Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
    'Updateby Extendoffice
        Application.Volatile
        Dim xRet As Variant 'could be an error
        Dim xCell As Range
        xRet = Application.Match(LookVal, FTable.Columns(1), FType)
        If IsError(xRet) Then
            VlookupComment = "Not Found"
        Else
            Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
            VlookupComment = xCell.Value
            With Application.Caller
                If Not .Comment Is Nothing Then
                    .Comment.Delete
                End If
                If Not xCell.Comment Is Nothing Then
                    .AddComment xCell.Comment.Text
                End If
            End With
        End If
    End Function
  3. Apoi salvați și închideți fereastra de cod.

Pasul 2: Introduceți formula pentru a obține rezultatul

  1. Acum, introduceți următoarea formulă și trageți mânerul de umplere pentru a copia această formulă în alte celule. Va returna atât valorile potrivite, cât și comentariile simultan, vezi captura de ecran:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

Notă: În formula de mai sus:

  • D2 este valoarea de căutare pe care doriți să-i returnați valoarea corespunzătoare;
  • A2: B9 este tabelul de date pe care doriți să îl utilizați;
  • 2 este numărul coloanei care conține valoarea potrivită pe care doriți să o returnați;
  • FALS indică obținerea unei potriviri exacte.

4.4 Numere VLOOKUP stocate ca text

De exemplu, am o serie de date în care numărul ID din tabelul original este în format numeric și numărul ID din celulele de căutare este stocat ca text, este posibil să întâmpinați o eroare #N/A când utilizați funcția normală VLOOKUP. În acest caz, pentru a prelua informațiile corecte, puteți include funcțiile TEXT și VALUE în cadrul funcției CĂUTARE V. Mai jos este formula pentru a realiza acest lucru:

Pasul 1: Aplicați și completați următoarea formulă

Aplicați următoarea formulă într-o celulă goală, apoi trageți mânerul de umplere în jos pentru a copia această formulă.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Rezultat:

Acum, veți obține rezultatele corecte, așa cum se arată mai jos:

note:

  • În formula de mai sus:
    • D2 este valoarea de căutare pe care doriți să-i returnați valoarea corespunzătoare;
    • A2: B8 este tabelul de date pe care doriți să îl utilizați;
    • 2 este numărul coloanei care conține valoarea potrivită pe care doriți să o returnați;
    • 0 indică obținerea unei potriviri exacte.
  • Această formulă funcționează bine și dacă nu sunteți sigur unde aveți numere și unde aveți text.

Cuprins