Funzione VLOOKUP in Excel - Guida per principianti: Sintassi è Esempii

Oghje avemu principiatu una seria di articuli chì descrizanu una di e funzioni più utili di Excel - VPR (CERCA V). Sta funzione, à u listessu tempu, hè unu di i più cumplessi è menu capitu.

In questu tutoriale nantu VPR Pruvaraghju di mette i principii u più simpliciamente pussibule per fà u prucessu di apprendimentu u più chjaru pussibule per l'utilizatori senza sperienza. Inoltre, studieremu parechji esempi cù formule Excel chì dimustrà i casi di usu più cumuni per a funzione VPR.

Funzione VLOOKUP in Excel - descrizzione generale è sintassi

Allora chì hè VPR? Ebbè, prima di tuttu, hè una funzione Excel. Chì faci ? Cerca u valore chì specificate è torna u valore currispundente da l'altra colonna. Tecnicamente parlante, VPR cerca u valore in a prima colonna di u intervallu datu è torna u risultatu da una altra colonna in a listessa fila.

In l'applicazione più cumuna, a funzione VPR cerca in a basa di dati per un identificatore unicu determinatu è estrae qualchì infurmazione ligata à questu da a basa di dati.

Prima lettera in u nome di a funzione VPR (VLOOKUP) significa Вverticale (Vverticale). Da ellu si pò distingue VPR da GPR (HLOOKUP), chì cerca un valore in a fila superiore di un intervallu - Гhorizontale (Horizzontalmente).

funzioni VPR dispunibule in Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, è Excel 2000.

Sintassi di a funzione VLOOKUP

funzioni VPR (VLOOKUP) hà a sintassi seguente:

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

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Comu pudete vede, una funzione VPR in Microsoft Excel hà 4 opzioni (o argumenti). I primi trè sò obligatorii, l'ultimu hè facultativu.

  • ricerca_valore (lookup_value) - U valore per circà. Questu pò esse un valore (numeru, data, testu) o un riferimentu di cellula (contene u valore di ricerca), o un valore restituitu da una altra funzione Excel. Per esempiu, sta formula cercà u valore 40:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

Se u valore di ricerca hè menu di u valore più chjucu in a prima colonna di a gamma chì hè cercata, a funzione VPR signalerà un errore #AT (#N/A).

  • table_array (tavula) - duie o più colonne di dati. Ricurdativi, a funzione VPR cerca sempre u valore in a prima colonna di u intervallu datu in l'argumentu table_array (tavula). U intervallu visibile pò cuntene parechji dati, cum'è testu, date, numeri, booleani. A funzione hè insensibile à u casu, significatu chì i caratteri majuscule è minuscule sò cunsiderate listessi. Allora a nostra formula cercà u valore 40 in cellule da A2 à A15, perchè A hè a prima colonna di a gamma A2: B15 datu in l'argumentu table_array (tavula):

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

  • col_index_num (column_number) hè u numeru di a colonna in u intervallu datu da quale u valore in a fila truvata serà tornatu. A colonna più a manca in u intervallu datu hè 1, a seconda colonna hè 2, a terza colonna hè 3 eccetera. Avà pudete leghje a formula sana:

    =VLOOKUP(40,A2:B15,2)

    =ВПР(40;A2:B15;2)

    Formula in cerca di valore 40 in a gamma A2: A15 è torna u valore currispundenti da a colonna B (perchè B hè a seconda colonna in a gamma A2: B15).

Se u valore di l'argumentu col_index_num (column_number) menu di 1tandu VPR signalerà un errore #VALU! (#VALORE!). È s'ellu hè più di u numeru di culonni in a gamma table_array (tavula), a funzione torna un errore #REF! (#LINK!).

  • range_lookup (range_lookup) - determina ciò chì circà:
    • match exact, argument deve esse uguali In (FALSE);
    • match apprussimativu, argumentu uguali CODICE VERA (TRUE) o micca specificatu in tuttu.

    Stu paràmetru hè opcional, ma assai impurtante. Più tardi in stu tutoriale VPR Vi mustraraghju alcuni esempi chì spieghendu cumu scrive formule per truvà partiti esatti è apprussimati.

VLOOKUP Esempii

Spergu chì a funzione VPR diventate un pocu più chjaru per voi. Avà fighjemu alcuni casi d'usu VPR in formule cù dati reali.

Cumu aduprà VLOOKUP per circà in un altru fogliu Excel

In pratica, formule cù una funzione VPR sò raramente usati per circà e dati nantu à a stessa foglia di travagliu. A più spessu, vi cercherete è ricuperà i valori currispondenti da un altru fogliu.

In u modu di utilizà VPR, cerca in un altru fogliu di Microsoft Excel, Devi in ​​l'argumentu table_array (tavula) specifica u nome di u fogliu cù un segnu d'esclamazione seguita da una gamma di cellule. Per esempiu, a formula seguente mostra chì a gamma A2: B15 hè nantu à un fogliu chjamatu Fola2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Di sicuru, u nome di u fogliu ùn deve esse inseritu manualmente. Basta principià à scrive a formula, è quandu si tratta di l'argumentu table_array (tavula), cambiate à u fogliu desideratu è selezziunate a gamma desiderata di cellule cù u mouse.

A formula mostrata in a screenshot sottu cerca u testu "Produttu 1" in a colonna A (hè a 1ª colonna di a gamma A2: B9) nantu à un fogliu di travagliu. prezzi.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Ricurdatevi chì quandu cercate un valore di testu, duvete chjudellu in virgulette (""), cum'è generalmente hè fattu in formule Excel.

Per argumentu table_array (tavula) hè desideratu sempre aduprà riferimenti assoluti (cù u segnu $). In questu casu, u intervallu di ricerca resta invariatu quandu si copia a formula in altre cellule.

Cerca in un altru libru di travagliu cù VLOOKUP

Per funziunà VPR travagliatu trà dui libri di Excel, avete bisognu di specificà u nome di u libru di travagliu in parentesi quadrate prima di u nome di u fogliu.

Per esempiu, quì sottu hè una formula chì cerca u valore 40 nantu à u fogliu Fola2 in u libru Numeri.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Eccu u modu più faciule per creà una formula in Excel cù VPRchì ligami à un altru libru di travagliu:

  1. Aprite i dui libri. Questu ùn hè micca necessariu, ma hè più faciule per creà una formula in questu modu. Ùn vulete micca entre in u nome di u libru di travagliu manualmente, ùn hè micca? Inoltre, vi prutegerà da i typos accidentali.
  2. Cumincià à scrive una funzione VPRè quandu si tratta di l'argumentu table_array (tavula), cambiate à un altru libru di travagliu è selezziunate a gamma di ricerca necessaria in questu.

A screenshot sottu mostra a formula cù a ricerca stabilita à un intervallu in u libru di travagliu PriceList.xlsx nantu à u fogliu prezzi.

funzioni VPR funzionerà ancu quandu chjude u libru di travagliu cercatu è u percorsu sanu à u schedariu di u libru di travagliu appare in a barra di formula, cum'è mostra quì sottu:

Se u nome di u libru di travagliu o fogliu cuntene spazii, allora deve esse chjusu in apostrofi:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Cumu utilizà un intervallu chjamatu o tabella in formule cù VLOOKUP

Se pensa à aduprà u listessu intervallu di ricerca in parechje funzioni VPR, pudete creà un intervallu chjamatu è entre u so nome in a formula cum'è argumentu table_array (tavula).

Per creà un intervallu chjamatu, simpricimenti selezziunate e cellule è inserite un nome appropritatu in u campu nome, à a manca di a barra di formula.

Avà pudete scrive a seguente formula per truvà u prezzu di un pruduttu U Produtu 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

A maiò parte di i nomi di intervalli funzionanu per tuttu u libru di Excel, per quessa, ùn ci hè bisognu di specificà u nome di foglia per l'argumentu. table_array (tavula), ancu s'è a formula è a gamma di ricerca sò nantu à diversi fogli di travagliu. S'elli sò in diversi libri di travagliu, allora prima di u nome di a gamma avete bisognu di specificà u nome di u libru di travagliu, per esempiu, cusì:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Allora a formula pare assai più chjara, d'accordu? Inoltre, l'usu di intervalli chjamati hè una bona alternativa à e referenze assolute, perchè u intervallu chjamatu ùn cambia micca quandu copiate a formula à altre cellule. Questu significa chì pudete esse sicuru chì a gamma di ricerca in a formula ferma sempre curretta.

Se cunvertite una gamma di cellule in un fogliu di calculu Excel cumpletu cù u cumandimu Table (Table) tab Inserzione (Inserisci), allora quandu selezziunate un intervallu cù u mouse, Microsoft Excel aghjunghje automaticamente i nomi di colonna (o u nome di a tavula se selezziunate a tavola sana) à a formula.

A formula finita serà simile à questu:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

O forse ancu cusì:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Quandu si usanu intervalli chjamati, i ligami puntanu à e stesse cellule, ùn importa induve copiate a funzione VPR in u libru di travagliu.

Utilizà Wildcards in Formule VLOOKUP

Cum'è cù parechje altre funzioni, VPR Pudete aduprà i seguenti caratteri jolly:

  • Segnu d'interrogazione (?) - rimpiazza ogni caratteru unicu.
  • Astérisque (*) - remplace toute séquence de caractères.

Utilizà i caratteri jolly in e funzioni VPR pò esse utile in parechji casi, per esempiu:

  • Quandu ùn vi ricurdate micca esattamente u testu chì avete bisognu di truvà.
  • Quandu vulete truvà una parolla chì face parte di u cuntenutu di una cellula. Sapete chì VPR cerca per u cuntenutu di a cellula in tuttu, cum'è se l'opzione hè attivata Abbinate tuttu u cuntenutu di a cellula (Cellula intera) in a ricerca standard di Excel.
  • Quandu una cellula cuntene spazi extra à u principiu o à a fine di u cuntenutu. In una tale situazione, pudete chjappà u vostru cervellu per un bellu pezzu, pruvatu à capisce perchè a formula ùn viaghja micca.

Esempiu 1: Cerchendu testu chì principia o finisci cù certi caratteri

Diciamu chì vulete cercà un cliente specificu in a basa di dati mostrata quì sottu. Ùn vi ricurdate micca u so cognome, ma sapete chì principia cù "ack". Eccu una formula chì farà u travagliu bè:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Avà chì site sicuru d'avè trovu u nome currettu, pudete aduprà a stessa formula per truvà a quantità pagata da stu cliente. Per fà questu, solu cambià u terzu argumentu di a funzione VPR à u numeru di a colonna desiderata. In u nostru casu, questu hè a colonna C (3e in a gamma):

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

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Eccu alcuni esempii più cù wildcards:

~ Truvate un nome chì finisci in "omu":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Truvate un nome chì principia cù "ad" è finisce cù "figliolu":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Truvemu u primu nomu in a lista, custituita da 5 caratteri:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Per funziunà VPR cù wildcards hà travagliatu bè, cum'è u quartu argumentu duvete sempre aduprà In (FALSE). Se l'intervalu di ricerca cuntene più di un valore chì currisponde à i termini di ricerca cù caratteri cumeddi, allora u primu valore truvatu serà tornatu.

Esempiu 2: Unisce i caratteri jolly è e referenze di cellula in formule VLOOKUP

Avà fighjemu un esempiu un pocu più cumplessu di cumu cercà cù a funzione VPR per valore in una cellula. Imagine chì a colonna A hè una lista di chjave di licenza, è a colonna B hè una lista di nomi chì pussede una licenza. Inoltre, avete una parte (parechji caratteri) di qualchì tipu di chjave di licenza in a cell C1, è vulete truvà u nome di u pruprietariu.

Questu pò esse fattu cù a seguente formula:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Questa formula cerca u valore da a cellula C1 in l'intervallu datu è torna u valore currispundente da a colonna B. Nota chì in u primu argumentu, usemu un carattere ampersand (&) prima è dopu a riferenza di cellula per ligà a stringa di testu.

Comu pudete vede in a figura sottu, a funzione VPR torna "Jeremy Hill" perchè a so chjave di licenza cuntene a sequenza di caratteri da a cell C1.

Nota chì l'argumentu table_array (tavula) in a screenshot sopra cuntene u nome di a tavola (Table7) invece di specificà un intervallu di cellule. Questu hè ciò chì avemu fattu in l'esempiu precedente.

Corrispondenza esatta o apprussimata in a funzione VLOOKUP

È infine, fighjemu un ochju più vicinu à l'ultimu argumentu chì hè specificatu per a funzione VPR - range_lookup (interval_view). Cumu l'anu dettu à u principiu di a lezziò, questu argumentu hè assai impurtante. Pudete ottene risultati cumplitamenti diffirenti in a stessa formula cù u so valore CODICE VERA (VERU) o In (FALSE).

Prima, scopre ciò chì Microsoft Excel significa per partiti esatti è apprussimati.

  • Se l'argumentu range_lookup (range_lookup) hè uguale à In (FALSE), a furmula cerca una corrispondenza esatta, vale à dì esattamente u listessu valore cum'è datu in l'argumentu ricerca_valore (ricerca_value). Sì in a prima colonna di a gamma table_array (tavula) scontra dui o più valori chì currispondenu à l'argumentu ricerca_valore (search_value), allora u primu serà sceltu. Se ùn si trova micca partite, a funzione signalerà un errore #AT (#N/A). Per esempiu, a seguente furmula signalerà un errore #AT (#N/A) s'ellu ùn ci hè micca valore in a gamma A2: A15 4:

    =VLOOKUP(4,A2:B15,2,FALSE)

    =ВПР(4;A2:B15;2;ЛОЖЬ)

  • Se l'argumentu range_lookup (range_lookup) hè uguale à CODICE VERA (TRUE), a formula cerca una corrispondenza apprussimata. Più precisamente, prima a funzione VPR cerca una corrispondenza esatta, è se nimu hè truvatu, selezziunate una apprussimata. Un match apprussimativu hè u valore più grande chì ùn supera micca u valore specificatu in l'argumentu. ricerca_valore (ricerca_value).

Se l'argumentu range_lookup (range_lookup) hè uguale à CODICE VERA (TRUE) o micca specificatu, allora i valori in a prima colonna di a gamma deve esse ordinati in ordine crescente, vale à dì da u più chjucu à u più grande. Altrimenti, a funzione VPR pò vultà un risultatu sbagliatu.

Per capisce megliu l'impurtanza di a scelta CODICE VERA (VERITÀ) o In (FALSE), fighjemu un pocu di più formule cù a funzione VPR è fighjate i risultati.

Esempiu 1: Truvà una Partita Esatta cù VLOOKUP

Comu vi ricordate, per circà una partita esatta, u quartu argumentu di a funzione VPR duverebbe impurtà In (FALSE).

Riturnemu à a tavula da u primu esempiu è scopre chì l'animali pò spustà à una veloce 50 miglia per ora. Credu chì sta formula ùn vi causarà micca difficultà:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Nota chì a nostra gamma di ricerca (colonna A) cuntene dui valori 50 - in cellule A5 и A6. A formula torna u valore da a cellula B5. Perchè? Perchè quandu cercate una partita esatta, a funzione VPR usa u primu valore trovu chì currisponde à quellu chì hè cercatu.

Esempiu 2: Utilizà VLOOKUP per Truvà una Partita Approssimata

Quandu si usa a funzione VPR per circà una partita apprussimata, vale à dì quandu l'argumentu range_lookup (range_lookup) hè uguale à CODICE VERA (TRUE) o omessi, a prima cosa chì duvete fà hè di sorte l'intervallu per a prima colonna in ordine crescente.

Questu hè assai impurtante perchè a funzione VPR torna u prossimu valore più grande dopu à quellu datu, è dopu a ricerca si ferma. Se trascurate l'ordinamentu currettu, finirete cù risultati assai strani o un missaghju d'errore. #AT (#N/A).

Avà pudete aduprà una di e seguenti formule:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Comu pudete vede, vogliu sapè quale di l'animali hà a velocità più vicina 69 miglia per ora. È quì hè u risultatu chì a funzione mi hà tornatu VPR:

Comu pudete vede, a formula hà tornatu un risultatu Antilope (Antilope), a cui vitezza 61 chilometri per ora, ancu s'è a lista include ancu Cheetah (Ghepardo) chì corre à velocità 70 miles per ora, è 70 hè più vicinu à 69 cà 61, ùn hè micca? Perchè questu succede? Perchè a funzione VPR quandu cercate una partita apprussimata, torna u valore più grande chì ùn hè micca più grande di quellu chì hè cercatu.

Spergu chì questi esempii ponu un pocu di luce nantu à u travagliu cù a funzione VPR in Excel, è ùn l'avete più guardatu cum'è un forasteru. Avà ùn hè micca male di ripetiri brevemente i punti chjave di u materiale chì avemu studiatu per risolve megliu in memoria.

VLOOKUP in Excel - avete bisognu di ricurdà questu!

  1. funzioni VPR Excel ùn pò micca vede à manca. Sempre cerca u valore in a colonna più a manca di u intervallu datu da l'argumentu table_array (tavula).
  2. In funzione VPR tutti i valori sò insensibili à u casu, vale à dì e lettere chjuche è grandi sò equivalenti.
  3. Se u valore chì cercate hè menu di u valore minimu in a prima colonna di a gamma chì hè cercata, a funzione VPR signalerà un errore #AT (#N/A).
  4. Se 3e argumentu col_index_num (column_number) menu di 1funziunava VPR signalerà un errore #VALU! (#VALORE!). S'ellu hè più grande ch'è u nùmeru di culonni in a gamma table_array (tavula), a funzione riportarà un errore #REF! (#LINK!).
  5. Aduprate referenze di cellula assoluta in l'argumentu table_array (tavula) in modu chì l'intervallu di ricerca currettu hè cunservatu quandu si copia a formula. Pruvate aduprà intervalli o tabelle chjamati in Excel cum'è alternativa.
  6. Quandu fate una ricerca apprussimata di partita, ricordate chì a prima colonna in a gamma chì cercate deve esse ordinata in ordine crescente.
  7. Infine, ricordate l'impurtanza di u quartu argumentu. Utilizà i valori CODICE VERA (VERITÀ) o In (FALSE) deliberatamente è vi sbarazzate di parechji mal di testa.

In i seguenti articuli di u nostru tutoriale di funzioni VPR in Excel, ampararemu esempi più avanzati, cum'è eseguisce diversi calculi utilizendu VPR, estrattendu valori da parechje colonne, è più. Grazie per avè lettu stu tutoriale è speru di vedevi torna a settimana prossima!

Lascia un Audiolibro