Come utilizzare le funzioni CERCA.VERT e CERCA.VERT in Excel 2016

Le più popolari delle funzioni di ricerca di Excel 2016 sono le funzioni CERCA.ORIZZ (per la ricerca orizzontale) e VLOOKUP (per la ricerca verticale). Queste funzioni si trovano nel menu a discesa Ricerca e riferimento nella scheda Formule della barra multifunzione e nella categoria Ricerca e riferimento nella finestra di dialogo Inserisci funzione. Fanno parte di un potente gruppo di funzioni che possono restituire valori cercandoli nelle tabelle di dati.

La funzione CERCA.VERT cerca verticalmente (dall'alto verso il basso) la colonna più a sinistra di una tabella di ricerca finché il programma non individua un valore che corrisponde o supera quello che stai cercando. La funzione CERCA.ORIZZ cerca orizzontalmente (da sinistra a destra) la riga più in alto di una tabella di ricerca finché non individua un valore che corrisponde o supera quello che stai cercando.

La funzione CERCA.VERT utilizza la seguente sintassi:

CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, [ricerca_intervallo])

La funzione CERCA.ORIZZ segue la sintassi quasi identica:

CERCA.ORIZZ(valore_ricerca,array_tabella,num_indice_riga,[ricerca_intervallo])

In entrambe le funzioni, l' argomento lookup_value è il valore che si desidera cercare nella tabella di ricerca e array_tabella è l'intervallo di celle o il nome della tabella di ricerca che contiene sia il valore da cercare che il valore correlato da restituire.

L' argomento col_index_num designa la colonna della tabella di ricerca contenente i valori restituiti dalla funzione CERCA.VERT in base alla corrispondenza del valore dell'argomento lookup_value con quelli nell'argomento array_tabella. Si determina l' argomento col_index_num contando quante colonne questa colonna si trova a destra della prima colonna della tabella di ricerca verticale e si include la prima colonna della tabella di ricerca in questo conteggio.

L' argomento riga_indice_num indica la riga contenente i valori restituiti dalla funzione CERCA.ORIZZ in una tabella orizzontale. Si determina l' argomento row_index_num contando il numero di righe inferiori a questa riga dalla riga superiore della tabella di ricerca orizzontale. Anche in questo caso, includi la riga superiore della tabella di ricerca in questo conteggio.

Quando si immettono gli argomenti col_index_num o row_index_num nelle funzioni VLOOKUP e HLOOKUP, il valore immesso non può superare il numero totale di colonne o righe nella tabella di ricerca.

L' argomento range_lookup facoltativo in entrambe le funzioni VLOOKUP e HLOOKUP è il logico VERO o FALSO che specifica se si desidera che Excel trovi una corrispondenza esatta o approssimativa per il valore_ricerca nella matrice_tabella. Quando si specifica VERO o si omette l' argomento range_lookup nella funzione VLOOKUP o HLOOKUP, Excel trova una corrispondenza approssimativa. Quando si specifica FALSE come argomento range_lookup , Excel trova solo le corrispondenze esatte.

La ricerca di corrispondenze approssimative riguarda solo quando stai cercando voci numeriche (piuttosto che testo) nella prima colonna o riga della tabella di ricerca verticale o orizzontale. Quando Excel non trova una corrispondenza esatta in questa colonna o riga di ricerca, individua il successivo valore più alto che non supera l' argomento lookup_value e quindi restituisce il valore nella colonna o riga designata dagli argomenti col_index_num o row_index_num .

Quando si utilizzano le funzioni CERCA.VERT e CERCA.ORIZZ, il testo o le voci numeriche nella colonna o riga di ricerca (ovvero la colonna più a sinistra di una tabella di ricerca verticale o la riga superiore di una tabella di ricerca orizzontale) devono essere univoci. Anche queste voci devono essere disposte o ordinate in ordine crescente; ovvero, ordine alfabetico per le voci di testo e ordine dal più basso al più alto per le voci numeriche.

La figura mostra un esempio di utilizzo della funzione CERCA.VERT per restituire una mancia del 15% o del 20% da una tabella delle mance, a seconda del totale al lordo delle imposte dell'assegno. La cella F3 contiene la funzione CERCA.VERT:

=CERCA.VERT(Pretax_Total,Tip_Table,IF(Tip_Percentage=0.15,2,3)) 

Questa formula restituisce l'importo della mancia in base alla percentuale di mancia nella cella F1 e l'importo al lordo delle imposte dell'assegno nella cella F2.

Utilizzo della funzione CERCA.VERT per restituire l'importo della mancia da aggiungere da una tabella di ricerca.

Per utilizzare questa tabella delle mance, inserisci la percentuale della mancia (15% o 20%) nella cella F1 (denominata Tip_Percentage) e l'importo dell'assegno al lordo delle imposte nella cella F2 (denominata Pretax_Total). Excel cerca quindi il valore immesso nella cella Pretax_Total nella prima colonna della tabella di ricerca, che include l'intervallo di celle A2: C101 ed è denominato Tip_Table.

Excel sposta quindi i valori nella prima colonna di Tip_Table finché non trova una corrispondenza, dopodiché il programma utilizza l' argomento col_index_num nella funzione CERCA.VERT per determinare quale importo di punta da quella riga della tabella per tornare alla cella F3. Se Excel rileva che il valore immesso nella cella Pretax_Total ($ 16,50 in questo esempio) non corrisponde esattamente a uno dei valori nella prima colonna di Tip_Table, il programma continua a cercare nell'intervallo di confronto finché non incontra il primo valore che supera il totale al lordo delle imposte (17,00 nella cella A19 in questo esempio). Excel torna quindi alla riga precedente nella tabella e restituisce il valore nella colonna che corrisponde all'argomento col_index_num della funzione VLOOKUP. (Questo perché range_lookup opzionaleargomento è stato omesso dalla funzione.)

Si noti che l'esempio della tabella dei suggerimenti nella figura utilizza una funzione SE per determinare l' argomento col_index_num per la funzione CERCA.VERT nella cella F3. La funzione SE determina il numero di colonna da utilizzare nella tabella dei suggerimenti facendo corrispondere la percentuale immessa in Tip_Percentage (cella F1) con 0,15. Se corrispondono, la funzione restituisce 2 come argomento col_index_num e la funzione VLOOKUP restituisce un valore dalla seconda colonna (la colonna 15% B) nell'intervallo Tip_Table. In caso contrario, la funzione IF restituisce 3 come argomento col_index_num e la funzione VLOOKUP restituisce un valore dalla terza colonna (la colonna 20% C) nell'intervallo Tip_Table.

La figura seguente mostra un esempio che utilizza la funzione CERCA.ORIZZ per cercare il prezzo di ogni articolo da forno memorizzato in una tabella di ricerca prezzi separata e quindi per restituire quel prezzo alla colonna Prezzo/Doz dell'elenco Vendite giornaliere. La cella F3 contiene la formula originale con la funzione CERCA.ORIZZ che viene quindi copiata nella colonna F:

Utilizzo della funzione CERCA.ORIZZ per restituire il prezzo di un articolo da forno da una tabella di ricerca.

=CERCA.ORIZZ(elemento,Tabella_prezzi,2,FALSO)

In questa funzione CERCA.ORIZZ, il nome dell'intervallo Item assegnato alla colonna Item nell'intervallo C3:C62 è definito come argomento lookup_value e il nome dell'intervallo di celle Tabella prezzi assegnato all'intervallo di celle I1:M2 è l' argomento array_tabella . L' argomento row_index_num è 2 perché si desidera che Excel restituisca i prezzi nella seconda riga della tabella di ricerca dei prezzi e l' argomento range_lookup facoltativo è FALSE perché il nome dell'articolo nell'elenco delle vendite giornaliere deve corrispondere esattamente al nome dell'articolo nella tabella di ricerca dei prezzi .

Se la funzione CERCA.ORIZZ utilizza l'intervallo della tabella dei prezzi per inserire il prezzo per dozzina per ogni articolo di prodotti da forno nell'elenco delle vendite giornaliere, è molto semplice aggiornare una qualsiasi delle vendite nell'elenco. Tutto quello che devi fare è modificare il suo costo Prezzo/Doz in questo intervallo e la funzione CERCA.ORIZZ aggiorna immediatamente il nuovo prezzo nell'elenco Vendite giornaliere ovunque l'articolo sia venduto.