La formula di Vlookup funziona solo quando la matrice della tabella nella formula non cambia, ma se c'è una nuova colonna inserita nella tabella o una colonna viene eliminata la formula dà un risultato errato o riflette un errore, per rendere la formula priva di errori tali situazioni dinamiche usiamo la funzione match per abbinare effettivamente l'indice dei dati e restituire il risultato effettivo.
Combina CERCA.VERT con Abbina
La formula di vlookup è la funzione più comunemente utilizzata per cercare e restituire lo stesso valore nell'indice di colonna specificato o il valore da un indice di colonna diverso con riferimento al valore corrispondente dalla prima colonna. La principale sfida affrontata durante l'utilizzo di vlookup è che l'indice di colonna da specificare è statico e non ha una funzionalità dinamica. Soprattutto quando si lavora su più criteri che richiedono di modificare manualmente l'indice della colonna di riferimento. In tal modo questa esigenza viene soddisfatta utilizzando la formula "CORRISPONDENZA" per avere una migliore presa o controllo sull'indice di colonna che cambia frequentemente nella formula CERCA.
VLookup e Match Formula
# 1 - Formula CERCA.VERT
La formula della funzione CERCA.VERT in Excel
Qui tutti gli argomenti da inserire sono obbligatori.
- Lookup_value : qui è necessario inserire la cella di riferimento o il testo con virgolette doppie per essere identificato nell'intervallo di colonne.
- Matrice della tabella : questo argomento richiede l'immissione dell'intervallo della tabella in cui cercare il valore_di ricerca ei dati da recuperare risiedono nell'intervallo della colonna specifico.
- Col_index_num - In questo argomento, è necessario immettere il numero di indice della colonna o il conteggio della colonna dalla prima colonna di riferimento da cui estrarre il valore corrispondente dalla stessa posizione del valore cercato nella prima colonna.
- [Range_lookup] - Questo argomento darà due opzioni.
- VERO - Corrispondenza approssimativa: - L'argomento può essere immesso come VERO o come numerico "1", che restituisce la corrispondenza approssimativa corrispondente alla colonna di riferimento o alla prima colonna. Inoltre, i valori nella prima colonna della matrice della tabella devono essere ordinati in ordine crescente.
- FALSE - Corrispondenza esatta: - Qui l'argomento da inserire può essere FALSE o numerico "0". Questa opzione restituirà solo la corrispondenza esatta del valore corrispondente da identificare dalla posizione nel primo intervallo di colonne. La mancata ricerca del valore dalla prima colonna restituirà un messaggio di errore "# N / D".
# 2 - Formula della partita
La funzione Match restituisce la posizione della cella del valore immesso per la matrice di tabella data.
Tutti gli argomenti all'interno della sintassi sono obbligatori.
- Lookup_value - Qui l'argomento inserito può essere il riferimento di cella del valore o una stringa di testo con virgolette doppie la cui posizione della cella deve essere estratta.
- Lookup_array : è necessario immettere l'intervallo di array per la tabella di cui si desidera identificare il valore o il contenuto della cella.
- [tipo di corrispondenza] - Questo argomento fornisce tre opzioni come spiegato di seguito.
- "1-Minore di" - Qui l'argomento da inserire è numerico "1" che restituirà il valore minore o uguale al valore di ricerca. E anche l'array di ricerca deve essere ordinato in ordine crescente.
- "0-Corrispondenza esatta" - Qui l'argomento da inserire deve essere numerico "0". Questa opzione restituirà la posizione esatta del valore di ricerca corrispondente. Tuttavia, la matrice di ricerca può essere in qualsiasi ordine.
- "-1-Maggiore di" - L'argomento da inserire deve essere numerico "-1". La terza opzione trova il valore più piccolo che è maggiore o uguale al valore di ricerca. Qui l'ordine per l'array di ricerca deve essere posto in ordine decrescente.
# 3 - CERCA.VERT con MATCH Formula
= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [range lookup])
Come utilizzare CERCA.VERT con la formula di corrispondenza in Excel?
L'esempio seguente aiuterà a capire il funzionamento della formula di vlookup e match durante la creazione.
Puoi scaricare questo VLookup con Match Excel Template qui - VLookup con Match Excel TemplateConsidera la tabella dati sottostante che descrive le specifiche del veicolo da acquistare.
Per ottenere la chiarezza della funzione combinata per vlookup e funzione di corrispondenza, capiamo come funziona la singola formula e quindi arriviamo ai risultati della corrispondenza di vlookup quando vengono messi insieme.
Passaggio 1: applichiamo la formula di vlookup a livello individuale per arrivare al risultato.
L'output è mostrato di seguito:
Qui il valore di ricerca è riferito a $ B9 che è il modello "E" e l'array di ricerca è dato come l'intervallo della tabella dati con valore assoluto "$", l'indice della colonna è riferito alla colonna "4" che è il conteggio per colonna "Tipo" e alla ricerca dell'intervallo viene data una corrispondenza esatta.
Pertanto, la seguente formula viene applicata per restituire il valore per la colonna "Carburante".
L'output è mostrato di seguito:
Qui il valore di ricerca con la stringa assoluta "$" applicata per il valore di ricerca e lookup_array aiuta a correggere la cella di riferimento anche se la formula viene copiata in una cella diversa. Nella colonna "Carburante", dobbiamo cambiare l'indice della colonna in "5" poiché il valore da cui i dati sono necessari per essere recuperati cambia.
Passaggio 2: applichiamo ora la formula Match per recuperare la posizione per il valore di ricerca specificato.
L'output è mostrato di seguito:
Come si può vedere nello screenshot qui sopra, qui stiamo cercando di recuperare la posizione della colonna dall'array della tabella. In questo caso, il numero di colonna da estrarre viene indicato come cella C8 che è la colonna "Tipo" e l'intervallo di ricerca da cercare viene fornito come intervallo di intestazioni di colonna e al tipo di corrispondenza viene assegnata una corrispondenza esatta come " 0 ".
Pertanto la tabella sottostante darà il risultato desiderato per le posizioni della colonna "Carburante".
Ora qui la colonna da ricercare viene data come cella D8 e l'indice di colonna desiderato viene restituito a "5".
Passaggio 3 : ora la formula Match verrà utilizzata all'interno della funzione vlookup per ottenere il valore dalla posizione della colonna identificata.
L'output è mostrato di seguito:
Nella formula sopra, la funzione di corrispondenza viene inserita al posto del parametro dell'indice di colonna della funzione vlookup. Qui la funzione di corrispondenza identificherà la cella di riferimento del valore di ricerca "C8" e restituirà il numero di colonna attraverso la matrice di tabella data. Questa posizione della colonna servirà come input per l'argomento dell'indice della colonna nella funzione vlookup. Quale a sua volta aiuterà vlookup a identificare il valore da restituire dal numero di indice della colonna risultante?
Allo stesso modo, abbiamo applicato vlookup con la formula di corrispondenza anche per la colonna "Carburante".
L'output è mostrato di seguito:
Possiamo quindi applicare questa funzione di combinazione anche ad altre colonne "Tipo" e "Carburante".
Cose da ricordare
- CERCA.VERT può essere applicato ai valori di ricerca solo nella parte più in alto a sinistra. Qualsiasi valore presente da cercare sul lato destro della tabella dati restituirà il valore di errore "# N / A".
- L'intervallo di table_array immesso nel secondo argomento dovrebbe essere il riferimento di cella assoluto "$", questo manterrà l'intervallo di array di tabelle fisso quando si applica la formula di ricerca ad altre celle, altrimenti le celle di riferimento per l'intervallo di array di tabelle passeranno alla cella successiva riferimento.
- Il valore immesso nel valore di ricerca non deve essere inferiore al valore più piccolo nella prima colonna della matrice della tabella, altrimenti la funzione restituirà il valore di errore "# N / A".
- Prima di applicare una corrispondenza approssimativa "TRUE" o "1" nell'ultimo argomento, ricordarsi sempre di ordinare la matrice della tabella in ordine crescente.
- La funzione match restituisce solo la posizione del valore nell'array della tabella vlookup e non restituisce il valore.
- Nel caso in cui la funzione di corrispondenza non sia in grado di identificare la posizione del valore di ricerca nella matrice della tabella, la formula restituisce "# N / A" nel valore di errore.
- Le funzioni Vlookup e match non fanno distinzione tra maiuscole e minuscole quando si abbina il valore di ricerca al valore di testo corrispondente nella matrice della tabella.
