Perchè INDEX è MATCH sò megliu cà VLOOKUP in Excel

Avemu spiegatu prima à i principianti cumu utilizà e funzioni basi di VLOOKUP (VLOOKUP inglese, l'abbreviazione significa "funzione di ricerca verticale"). È l'utilizatori sperimentati sò stati mostrati parechje formule più cumplicate.

È in questu articulu avemu da pruvà à dà infurmazione nantu à un altru mètudu di travaglià cù a ricerca verticale.

Puderete esse dumandate: "Perchè hè questu necessariu?". È questu hè necessariu per vede tutti i metudi di ricerca pussibuli. Inoltre, numerosi restrizioni VLOOKUP spessu impediscenu di ottene u risultatu desideratu. In questu sensu, INDEX ( ) MATCH ( ) hè assai più funziunale è diversu, è anu ancu menu restrizioni.

Basics INDEX MATCH

Dapoi u scopu di sta guida hè di mustrà quantu bona sta funziunalità hè, noi Fighjemu l'infurmazioni basi nantu à i principii di u so funziunamentu. E noi vi mustrarà esempii, è ancu cunsiderà perchè, hè megliu chè VLOOKUP ().

INDEX Funzione Sintassi è Usu

Questa funzione aiuta à truvà u valore desideratu trà e zone di ricerca specificate basatu nantu à a colonna o u numeru di linea. Sintassi:

= INDEX (matrice, numeru di fila, numeru di colonna):

• array - l'area in quale a ricerca serà fatta;
• line number - u numeru di a linea chì deve esse cercatu in u array specificatu. Se u numeru di fila hè scunnisciutu, u numeru di colonna deve esse specificatu;
• numero di colonna - u numeru di a colonna chì si trova in a matrice specifica. Se u valore hè scunnisciutu, un numeru di linea hè necessariu.

Un esempiu di una formula simplice:

=INDICE(A1:S10,2,3;XNUMX;XNUMX)

A funzione cercarà in a gamma da A1 à C10. I numeri mostranu da quale fila (2) è colonna (3) mostra u valore desideratu. U risultatu serà a cellula C2.

Piuttostu simplice, nò? Ma quandu travagliate cù documenti veri, hè improbabile di avè infurmazione nantu à i numeri di colonna o cellule. Hè per quessa a funzione MATCH () hè per.

Sintassi di Funzione MATCH è Usu

A funzione MATCH () cerca u valore desideratu è mostra u so numeru apprussimativu in l'area di ricerca specifica.

A sintassi searchpos() s'assumiglia cusì:

= MATCH (valore à circà, array à circà, tippu di cuncurrenza)

• valore di ricerca - u numeru o testu per esse truvatu;
• searched array - l'area induve a ricerca serà fatta;
• tippu di match - specifica se cercà u valore esatta o i valori più vicinu à questu:
  • 1 (o nisun valore specificatu) - rende u valore più grande chì hè uguale o menu di u valore chì hè statu specificatu;
  • 0 - mostra una corrispondenza esatta cù u valore cercatu. In a cumminazzioni INDEX () MATCH () avete quasi sempre bisognu di una partita esatta, cusì scrivemu 0;
  • -1 - Mostra u valore più chjucu chì hè più grande o uguale à u valore specificatu in a formula. A classificazione hè fatta in ordine decrescente.

Per esempiu, in a gamma B1: B3 New York, Parigi, Londra sò registrati. A furmula quì sottu mostrarà u numeru 3 perchè Londra hè u terzu in a lista:

= EXPOSI (Londra,B1:B3,0)

Cumu travaglià cù a funzione INDEX MATCH 

Probabilmente avete digià principiatu à capisce u principiu da quale u travagliu cumuni di sti funzioni hè custruitu. In breve, allora INDEX() cerca u valore desideratu trà e fila è colonne specificate. È MATCH() mostra i numeri di sti valori:

=INDEX (colonna da quale u valore hè tornatu, MATCH (valore à circà, colonna à circà, 0))

Avete sempre difficultà à capisce cumu funziona? Forse un esempiu spiegherà megliu. Supponete chì avete una lista di capitali mundiali è a so populazione:

Per sapè a dimensione di a pupulazione di una certa capitale, per esempiu, a capitale di u Giappone, usemu a seguente formula:

=INDICE(C2:C10, MATCH(Giappone, A2:A10,0))

Spiegazione:

• A funzione MATCH () cerca u valore - "Giappone" in l'array A2: A10 è torna u numeru 3, perchè u Giappone hè u terzu valore in a lista. 
• Sta figura va ànumeru di linea” in a formula INDEX () è dice à a funzione per stampà un valore da sta fila.

Allora a formula sopra diventa a formula standard INDICE (C2:C10,3). A furmula cerca da e cellule C2 à C10 è torna dati da a terza cellula in questa gamma, vale à dì, C4, perchè u countdown principia da a seconda fila.

Ùn vogliu micca prescrive u nome di a cità in a formula? Allora scrivite in ogni cellula, dì F1, è l'utilizate cum'è riferimentu in a formula MATCH (). È finisci cù una formula di ricerca dinamica:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Impurtante! Numaru di linii in array INDEX() deve esse u listessu cum'è u numeru di fila in matrice cunsiderata in MATCH(), altrimenti averete u risultatu sbagliatu.

Aspetta un minutu, perchè micca solu aduprà a formula VLOOKUP () ?

=CHERCHEV(F1, A2:C10, 3, False)

 Chì ghjè u puntu di perde u tempu à pruvà à capisce tutte queste cumplessità di INDEX MATCH ?

In questu casu, ùn importa micca a funzione à aduprà. Questu hè solu un esempiu per capisce cumu e funzioni INDEX () è MATCH () funzionanu inseme. Altri esempi mostraranu ciò chì queste funzioni sò capaci in situazioni induve u VLOOKUP hè impotente. 

INDICE MATCH o VLOOKUP

Quandu decide quale formula di ricerca aduprà, assai accunsenu chì INDEX () è MATCH () sò assai superiori à VLOOKUP. Tuttavia, assai persone anu sempre aduprà VLOOKUP (). Prima, VLOOKUP () hè più simplice, è secondu, l'utilizatori ùn capiscenu micca tutti i vantaghji di travaglià cù INDEX () è MATCH (). Senza sta cunniscenza, nimu ùn accunsente à passà u so tempu à studià un sistema cumplessu.

Eccu i vantaghji chjave di INDEX () è MATCH () sopra VLOOKUP ():

• Ricerca da diritta à manca. VLOOKUP () ùn pò micca cercà da a diritta à a manca, cusì i valori chì cercate deve esse sempre in e colonne più manca di a tavola. Ma INDEX () è MATCH () ponu trattà questu senza prublema. Questu articulu vi diciarà ciò chì pare in a pratica: cumu truvà u valore desideratu à u latu manca.

1. Aggiunta sicura o eliminazione di colonne. A formula VLOOKUP () mostra risultati sbagliati quandu si sguassate o aghjunghje colonne perchè VLOOKUP () hà bisognu di u numeru di colonna esatta per esse successu. Naturalmente, quandu i culonni sò aghjuntu o eliminati, i so numeri cambianu ancu. 

È in e formule INDEX () è MATCH (), hè specificatu un intervallu di colonne, micca colonne individuali. In u risultatu, pudete aghjunghje è caccià e colonne in modu sicuru senza avè da aghjurnà a formula ogni volta.

1. Nisun limite à i volumi di ricerca. Quandu si usa VLOOKUP (), u numeru tutale di criteri di ricerca ùn deve micca più di 255 caratteri o uttene un #VALORE! Allora se i vostri dati cuntenenu un gran numaru di caratteri, INDEX () è MATCH () sò a megliu opzione.
2. Alta velocità di trasfurmazioni. Se i vostri tavulini sò relativamente chjuchi, allora hè improbabile di nutà alcuna differenza. Ma, se a tavula cuntene centinaie o millaie di fila, è, per quessa, ci sò centinaie è millaie di formule, INDEX () è MATCH () affruntà assai più veloce di VLOOKUP (). U fattu hè chì Excel prucederà solu e culonni specificati in a formula, invece di trasfurmà a tavola sana. 

L'impattu di u rendiment di VLOOKUP () serà soprattuttu notevule se u vostru fogliu di travagliu cuntene un gran numaru di formule cum'è VLOOKUP () è SUM (). Cuntrolli separati di e funzioni VLOOKUP () sò necessarii per analizà ogni valore in un array. Allora Excel hà da processà una quantità enorme di informazioni, è questu rallenta u travagliu significativamente.

Esempi di formule 

Avemu digià capitu l'utilità di sti funzioni, cusì pudemu passà à a parte più interessante: l'applicazione di a cunniscenza in a pratica.

Formula per circà da diritta à manca

Comu digià dettu, VLOOKUP ùn pò micca fà sta forma di ricerca. Dunque, se i valori desiderati ùn sò micca in a colonna più a manca, VLOOKUP () ùn pruducerà micca un risultatu. E funzioni INDEX () è MATCH () sò più versatili, è u locu di i valori ùn hà micca un rolu maiò per elli à travaglià.

Per esempiu, aghjustemu una colonna di gradu à u latu manca di a nostra tavula è pruvate à scopre quale rangu in termini di populazione occupa a capitale di u Nostru Paese.

In a cellula G1, scrivemu u valore per esse truvatu, è dopu aduprate a seguente formula per circà in a gamma C1: C10 è rinvià u valore currispundente da A2: A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Prompt. Se pensa à aduprà sta formula per parechje cellule, assicuratevi di risolve i intervalli cù l'indirizzu assolutu (per esempiu, $A$2: $A$10 è $C$2: 4C$10).

INDICE PIÙ EXPUSI MORE EXPOSED  per circà in colonne è fila

In l'esempii sopra, avemu usatu queste funzioni cum'è un sustitutu di VLOOKUP () per rinvià i valori da una gamma predefinita di fila. Ma chì si avete bisognu di fà una matrice o una ricerca à dui lati?

Sembra complicatu, ma a formula per tali calculi hè simile à a formula standard INDEX() MATCH(), cù una sola diferenza: a formula MATCH() deve esse usata duie volte. A prima volta per uttene u numeru di fila, è a seconda volta per uttene u numeru di colonna:

=INDICE (matrice, MATCH (valore di ricerca verticale, colonna di ricerca, 0), MATCH (valore di ricerca horizontale, fila di ricerca, 0))

Fighjemu a tavula sottu è pruvate à fà una formula INDEX () EXPRESS () EXPRESS () per vede a demografia in un paese specificu per un annu sceltu.

U paese di destinazione hè in a cellula G1 (cerca verticale) è l'annu di destinazione hè in a cellula G2 (cerca horizontale). A formula sarà cusì:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Cumu funziona sta formula

Cum'è cù qualsiasi altre formule cumplesse, sò più faciuli di capiscenu sparghjendu in equazioni individuali. E poi pudete capisce ciò chì ogni funzione individuale faci:

• MATCH (G1,A2:A11,0) - cerca un valore (G1) in a gamma A2: A11 è mostra u numeru di stu valore, in u nostru casu hè 2;
• RICERCA (G2,B1:D1,0) – cerca un valore (G2) in a gamma B1:D1. In questu casu, u risultatu hè 3.

I numeri di fila è colonna truvati sò mandati à u valore currispundenti in a formula INDEX ():

=INDICE(B2:D11,2,3;XNUMX;XNUMX)

In u risultatu, avemu un valore chì hè in una cellula à l'intersezzione di 2 fila è 3 culonni in a gamma B2: D11. È a formula mostra u valore desideratu, chì hè in a cellula D3.

Ricerca per parechje cundizioni cù INDEX è MATCH

Sè avete lettu a nostra guida à VLOOKUP (), probabilmente avete pruvatu parechje formule di ricerca. Ma stu metudu di ricerca hà una limitazione significativa - a necessità di aghjunghje una colonna ausiliaria.

Ma a bona nutizia hè chì Cù INDEX () è MATCH () pudete cercà parechje cundizioni senza avè da edità o cambià u vostru fogliu di travagliu.

Eccu a formula generale di ricerca multi-condizione per INDEX () MATCH ():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

A nota: sta furmula deve esse usata inseme cù a scurciatoia di tastiera CTRL+SHIFT+ENTER.

Eppo supponi chì avete bisognu di truvà u valore chì cercate basatu annantu à 2 cundizioni: Tessili и Pruduttu.

Questu hè bisognu di a seguente formula:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

In questa formula, C2: C10 hè a gamma in quale a ricerca serà fatta, F1 - sta cundizione, A2: A10 - hè a gamma per paragunà a cundizione, F2 - cundizione 2, V2: V10 - intervallu per paragunà a cundizione 2.

Ùn vi scurdate di pressu a cumminazzioni à a fine di u travagliu cù a formula CTRL + MAIUSC + ENTER - Excel chjude automaticamente a formula cù parentesi ricci, cum'è mostra in l'esempiu:

Se ùn vulete micca aduprà una formula di array per u vostru travagliu, aghjunghje un altru INDEX () à a formula è appughjà ENTER, serà cum'è in l'esempiu:

Cumu funziona sti formule

Questa formula funziona in u listessu modu cum'è a formula standard INDEX () MATCH (). Per circà parechje cundizioni, basta à creà parechje cundizioni Falsi è Veri chì rapprisentanu e cundizioni individuali curretti è sbagliati. E poi sti cundizioni s'applicanu à tutti l'elementi currispundenti di l'array. A formula cunverte l'argumenti False è True in 0 è 1, rispettivamente, è produce un array induve 1 hè i valori currispondenti chì sò stati truvati in a stringa. MATCH() truvà u primu valore chì currisponde à 1 è u passa à a formula INDEX (). È, à u turnu, restituverà u valore digià desideratu in a linea specifica da a colonna desiderata.

Una formula senza un array dipende da a capacità di INDEX () per trattà da sè stessu. U sicondu INDEX () in a formula currisponde à falsità (0), cusì passa tutta a matrice cù quelli valori à a formula MATCH (). 

Questa hè una spiegazione abbastanza longa di a logica daretu à sta formula. Per più infurmazione leghje l'articulu "INDICE MATCH cù parechje cundizioni».

MEDIA, MAX è MIN in INDICE è MATCH

Excel hà e so funzioni speciali per truvà medii, massimi è minimi. Ma chì si vulete acquistà dati da a cellula assuciata à quelli valori? In stu casu AVERAGE, MAX è MIN deve esse usatu in cunjunzione cù INDEX è MATCH.

INDICE MATCH è MAX

Per truvà u valore più grande in a colonna D è vede in a colonna C, utilizate a formula: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDICE MATCH è MIN

Per truvà u valore più chjucu in a colonna D è vede in a colonna C, utilizate a seguente formula:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX è SERPENT

Per truvà u valore mediu in a colonna D è vede stu valore in C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Sicondu cumu i vostri dati sò scritti, u terzu argumentu per MATCH () hè 1, 0 o -1:

• se i culonni sò ordinati in ordine ascendente, stabilisce 1 (tandu a formula calcularà u valore massimu, chì hè menu o uguale à u valore mediu);
• se l'ordine hè discendente, allora -1 (a furmula pruducia u valore minimu chì hè più grande o uguale à a media);
• se l'array di ricerca cuntene un valore chì hè esattamente uguale à a media, allora mette à 0. 

 In u nostru esempiu, a pupulazione hè ordinata in ordine decrescente, cusì mettemu -1. È u risultatu hè Tokyo, postu chì u valore di a pupulazione (13,189 000) hè u più vicinu à u valore mediu (XNUMX).

VLOOKUP () pò ancu fà tali calculi, ma solu cum'è una formula di matrice: VLOOKUP cù AVERAGE, MIN è MAX.

INDICE MATCH è ESND/IFERROR

Probabilmente avete digià nutatu chì se a formula ùn pò micca truvà u valore desideratu, tira un errore # N / A. Pudete rimpiazzà u missaghju d'errore standard cù qualcosa di più informativu. Per esempiu, stabilisce l'argumentu in a formula In u XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Cù sta formula, s'è vo inserite dati chì ùn hè micca in a tavula, a forma vi darà u missaghju specificatu.

Sè vo vulete catturà tutti l'errori, allora fora di In u XNUMX pò esse usatu IFERROR:

=IFERROR(INDICE(C2:C10,MATCH(F1,A2:A10,0)), "Qualcosa hè andatu male!")

Ma ricordate chì l'errore di maschera in questu modu ùn hè micca una bona idea, perchè l'errori standard signalanu violazioni in a formula.

Speremu chì avete trovu a nostra guida per utilizà a funzione INDEX MATCH() utile.