La formula di Vlookup funziona solo quando la matrice della tabella nella formula non cambia, ma se è presente una nuova colonna inserita nella tabella o una colonna viene eliminata la formula fornisce un risultato errato o riflette un errore, per rendere la formula priva di errori in in 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, la cella di riferimento o il testo con virgolette doppie devono essere inseriti per essere identificati 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: - In questo caso, 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" - In questo caso, l'argomento da inserire è numerico "1", che restituirà il valore minore o uguale al valore di ricerca. Inoltre, l'array di ricerca deve essere ordinato in ordine crescente.
- "0-Corrispondenza esatta": qui, l'argomento da inserire deve essere il numero "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.
Considera 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 una volta 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 di dati con valore assoluto "$" l'indice della colonna è riferito alla colonna "4" che è il conteggio per colonna "Tipo" e alla ricerca dell'intervallo viene assegnata 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.
Passo # 2 - Ora, applichiamo la formula Match per recuperare la posizione per il dato valore di ricerca.
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 è 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 da come "0".
Pertanto, la tabella seguente darà il risultato desiderato per le posizioni della colonna "Carburante".
Ora qui, la colonna da cercare viene data come cella D8 e l'indice di colonna desiderato viene restituito come "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. Che, 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 alle 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 fisso della matrice della tabella quando si applica la formula di ricerca ad altre celle, altrimenti le celle di riferimento per l'intervallo della matrice della tabella 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.
- Vlookup e le funzioni di corrispondenza non fanno distinzione tra maiuscole e minuscole quando si abbina il valore di ricerca con il valore di testo corrispondente nella matrice della tabella.
