Căutarea valorilor cu VLOOKUP, INDEX sau MATCH

Sfat: Încercați să utilizați noile funcții XLOOKUP și XMATCH , versiuni îmbunătățite ale funcțiilor descrise în acest articol. Aceste funcții noi funcționează în orice direcție și returnează în mod implicit potriviri exacte, ceea ce le face mai ușor și mai convenabil de utilizat decât predecesorii lor.

Să presupunem că aveți o listă de numere de locație Office și trebuie să știți ce angajați sunt în fiecare birou. Foaia de calcul este uriașă, așadar este posibil să vă gândiți că este o activitate provocatoare. De fapt, este foarte ușor să faceți acest lucru cu o funcție de căutare.

Funcțiile VLOOKUP și HLOOKUP , împreună cu index și Match,sunt câteva dintre cele mai utile funcții din Excel.

Notă: Caracteristica expert căutare nu mai este disponibilă în Excel.

Iată un exemplu de utilizare a VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

În acest exemplu, B2 este primul argument-un element de date la care trebuie să funcționeze funcția. Pentru VLOOKUP, primul argument este valoarea pe care doriți să o găsiți. Acest argument poate fi o referință de celulă sau o valoare fixă, cum ar fi "Smith" sau 21.000. Al doilea argument este zona de celule, C2-: E7, în care să căutați valoarea pe care doriți să o găsiți. Al treilea argument este coloana din zona de celule care conține valoarea pe care o căutați.

Al patrulea argument este opțional. Introduceți fie TRUE, fie FALSE. Dacă introduceți True sau lăsați argumentul necompletat, funcția returnează o potrivire aproximativă a valorii pe care o specificați în primul argument. Dacă introduceți FALSE, funcția va corespunde cu valoarea furnizată de primul argument. Cu alte cuvinte, dacă lăsați necompletat al patrulea argument-sau introduceți TRUE, vă oferă mai multă flexibilitate.

Acest exemplu vă arată cum funcționează funcția. Atunci când introduceți o valoare în celula B2 (primul argument), VLOOKUP caută celulele din zona C2: E7 (al doilea argument) și returnează cea mai apropiată corespondență aproximativă din a treia coloană din zonă, coloana E (al treilea argument).

O utilizare tipică a funcției VLOOKUP

Al patrulea argument este gol, astfel încât funcția returnează o corespondență aproximativă. Dacă nu ar returna nimic, ar trebui să introduceți una din valorile din coloanele C și D pentru a obține un rezultat.

Atunci când sunteți familiarizat cu VLOOKUP, funcția HLOOKUP este la fel de ușor de utilizat. Introduceți aceleași argumente, dar caută în rânduri în loc de coloane.

Utilizarea INDEXului și a POTRIVIRIlor în loc de VLOOKUP

Există anumite limite cu utilizarea VLOOKUP-funcția VLOOKUP poate căuta doar o valoare de la stânga la dreapta. Acest lucru înseamnă că coloana care conține valoarea pe care o căutați trebuie să fie întotdeauna situată în partea stângă a coloanei care conține valoarea returnată. Acum, dacă foaia de calcul nu este construită în acest fel, atunci nu utilizați VLOOKUP. Utilizați în schimb combinația dintre funcțiile INDEX și MATCH.

Acest exemplu ilustrează o listă mică în care valoarea după care dorim să căutăm, Chicago, nu se află în coloana cea mai din stânga. Așadar, nu putem utiliza VLOOKUP. În schimb, vom utiliza funcția MATCH pentru a găsi Chicago în zona B1:B11. Acesta se află în rândul 4. Apoi, INDEX utilizează acea valoare ca argument de căutare și găsește populația pentru Chicago în coloana 4 (coloana D). Formula utilizată este afișată în celula A14.

Utilizarea funcțiilor INDEX și MATCH pentru a căuta o valoare

Pentru mai multe exemple de utilizare a INDEXului și a POTRIVIRIlor în loc de VLOOKUP, consultați articolul https://www.mrexcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ de Bill Jelen, Microsoft MVP.

Încercați

Dacă doriți să experimentați cu funcții de căutare înainte să le încercați cu propriile date, iată câteva date eșantion.

Exemplu VLOOKUP la locul de muncă

Copiați următoarele date într-o foaie de calcul necompletată.

Sfat: Înainte să lipiți datele în Excel, setați lățimea coloanelor de la A la C la 250 pixeli, apoi faceți clic pe încadrare text (filapornire , grupul Aliniere ).

Densitate

Vâscozitate

Temperatură

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Formulă

Descriere

Rezultat

=VLOOKUP(1;A2:C10;2)

Utilizând o potrivire aproximativă, caută valoarea 1 în coloana A, găsește cea mia mare valoare mai mică sau egală cu 1 în coloana A, care este 0,946, apoi returnează valoarea din coloana B în același rând.

2,17

=VLOOKUP(1;A2:C10;3;TRUE)

Utilizând o potrivire aproximativă, caută valoarea 1 în coloana A, găsește cea mai mare valoare mai mică sau egală cu 1 în coloana A, care este 0,946, apoi returnează valoarea din coloana C din același rând.

100

=VLOOKUP(0,7;A2:C10;3;FALSE)

Utilizând o potrivire exactă, caută valoarea 0,7 în coloana A. Deoarece nu există nicio potrivire exactă în coloana A, se returnează o eroare.

#N/A

=VLOOKUP(0,1;A2:C10;2;TRUE)

Utilizând o potrivire aproximativă, caută valoarea 0,1 în coloana A. Deoarece 0,1 este o valoare mai mică decât cea mai mică valoare din coloana A, se returnează o eroare.

#N/A

=VLOOKUP(2;A2:C10;2;TRUE)

Utilizând o potrivire aproximativă, caută valoarea 2 în coloana A, găsește cea mai mare valoare mai mică sau egală cu 2 din coloana A, care este 1,29, apoi returnează valoarea din coloana B din același rând.

1,71

Exemplu HLOOKUP

Copiați toate celulele din acest tabel și lipiți-le în celula A1 într-o foaie de lucru necompletată în Excel.

Sfat: Înainte să lipiți datele în Excel, setați lățimea coloanelor de la A la C la 250 pixeli, apoi faceți clic pe încadrare text (filapornire , grupul Aliniere ).

Osii

Rulmenți

Șuruburi

4

4

9

5

7

10

6

8

11

Formulă

Descriere

Rezultat

=HLOOKUP("Osii"; A1:C4; 2; TRUE)

Caută „Osii” în rândul 1 și returnează valoarea din rândul 2 ce este în aceeași coloană (coloana A).

4

=HLOOKUP("Rulmenți"; A1:C4; 3; FALSE)

Caută „Rulmenți” în rândul 1 și returnează valoarea din rândul 3 ce este în aceeași coloană (coloana B).

7

=HLOOKUP("B"; A1:C4; 3; TRUE)

Caută „R” în rândul 1 și returnează valoarea din rândul 3 ce este în aceeași coloană. Pentru că nu se găsește o potrivire exactă pentru „B”, se utilizează cea mai mare valoare din rândul 1 care este mai mică decât „B”: „Osii”, din coloana A.

5

=HLOOKUP("Șuruburi"; A1:C4; 4)

Caută „Șuruburi” în rândul 1 și returnează valoarea din rândul 4 ce este în aceeași coloană (coloana C).

11

=HLOOKUP(3; {1;2;3\"a";"b";"c"\"d";"e";"f"}; 2; TRUE)

Caută numărul 3 în constanta matrice de trei rânduri și returnează valoarea din rândul 2 aflată în aceeași coloană (în acest caz cea de-a treia). Sunt trei rânduri de valori în constanta matrice, fiecare rând fiind separat de punct și virgulă (;). Deoarece "c" se găsește în rândul 2 și în aceeași coloană ca 3, se returnează „c”.

c

Exemple de indici și POTRIVIRI

Acest ultim exemplu utilizează funcțiile INDEX și MATCH împreună pentru a returna cel mai vechi număr de factură și data corespunzătoare pentru fiecare dintre cele cinci orașe. Deoarece data este returnată ca număr, utilizăm funcția TEXT pentru a o formata ca dată. Funcția INDEX utilizează, de fapt, rezultatul funcției MATCH drept argument. Combinația funcțiilor INDEX și MATCH se utilizează de două ori în fiecare formulă - mai întâi, pentru a returna numărul facturii, apoi pentru a returna data.

Copiați toate celulele din acest tabel și lipiți-le în celula A1 într-o foaie de lucru necompletată în Excel.

Sfat: Înainte să lipiți datele în Excel, setați lățimea coloanelor de la A la D la 250 pixeli, apoi faceți clic pe încadrare text (filapornire , grupul Aliniere ).

Factură

Localitate

Data facturării

Cea mai veche factură în funcție de localitate, cu data

3115

Alba-Iulia

07.04.12

="Arad = "&INDEX($A$2:$C$33;MATCH("Arad";$B$2:$B$33;0);1)& ", Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("Arad";$B$2:$B$33;0);3);"dd.mm.yy")

3137

Alba-Iulia

09.04.12

="Arad = "&INDEX($A$2:$C$33;MATCH("Arad";$B$2:$B$33;0);1)& ", Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("Arad";$B$2:$B$33;0);3);"dd.mm.yy")

3154

Alba-Iulia

11.04.12

="Domnești = "&INDEX($A$2:$C$33;MATCH("Domnești";$B$2:$B$33;0);1)& ", Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("Domnești";$B$2:$B$33;0);3);"dd.mm.yy")

3191

Alba-Iulia

21.04.12

="Cluj-Napoca = "&INDEX($A$2:$C$33;MATCH("Cluj-Napoca";$B$2:$B$33;0);1)& "; Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("Cluj-Napoca";$B$2:$B$33;0);3);"dd.mm.yy")

3293

Alba-Iulia

25.04.12

="București = "&INDEX($A$2:$C$33;MATCH("București";$B$2:$B$33;0);1)& "; Dată factură: " & TEXT(INDEX($A$2:$C$33;MATCH("București";$B$2:$B$33;0);3);"dd.mm.yy")

3331

Alba-Iulia

27.04.12

3350

Alba-Iulia

28.04.12

3390

Alba-Iulia

01.05.12

3441

Alba-Iulia

02.05.12

3517

Arad

08.05.12

3124

Austin

09.04.12

3155

Austin

11.04.12

3177

Austin

19.04.12

3357

Austin

28.04.12

3492

Arad

06.05.12

3316

Domnești

25.04.12

3346

Domnești

28.04.12

3372

Domnești

01.05.12

3414

Domnești

01.05.12

3451

Domnești

02.05.12

3467

Domnești

02.05.12

3474

Domnești

04.05.12

3490

Domnești

05.05.12

3503

Domnești

08.05.12

3151

New Orleans

09.04.12

3438

New Orleans

02.05.12

3471

Cluj-Napoca

04.05.12

3160

Tampa

18.04.12

3328

Tampa

26.04.12

3368

Tampa

29.04.12

3420

Tampa

01.05.12

3501

București

06.05.12

Consultați și

Cartelă de referințe rapide:

funcțiile de căutare și de referință

pentru VLOOKUP, utilizați argumentul TABLE_ARRAY într-o funcție VLOOKUP

Extindeți-vă competențele Office
Explorați instruirea
Fiți primul care obține noile caracteristici
Alăturați-vă utilizatorilor Office Insider

Au fost utile aceste informații?

Vă mulțumim pentru feedback!

Vă mulțumim pentru feedback! Se pare că ar fi util să luați legătura cu unul dintre agenții noștri de asistență Office.

×