Nella funzione VLOOKUP, spesso definiamo col_index_no static. Lo codifichiamo all'interno della formula CERCA.VERT, come CERCA.VERT(id,data,3,0). Il problema sorge quando inseriamo o eliminiamo una colonna all'interno dei dati. Se rimuoviamo o aggiungiamo una colonna prima o dopo la 3a colonna, la 3a colonna non farà più riferimento alla colonna prevista. Questo è un problema. Altro è quando hai più colonne da cercare. Dovrai modificare l'indice di colonna in ogni formula. Il semplice copia-incolla non aiuterà.
Ma che ne dici, se puoi dire a CERCA.VERT di guardare le intestazioni e restituire solo il valore delle intestazioni corrispondenti. Questo è chiamato VLOOKUP bidirezionale.
Ad esempio, se ho una formula CERCA.VERT per ilsegni colonna, quindi CERCA.VERT dovrebbe cercare segni colonna nei dati e restituisce il valore da quella colonna. Questo risolverà il nostro problema.
Hmm… Ok, quindi come lo facciamo? Usando la funzione Match all'interno della funzione VLOOKUP.
Formula generica
=CERCA.VERT(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Valore di ricerca: il valore di ricerca nella prima colonna di array_tabella.
Matrice di tabella: l'intervallo in cui si desidera eseguire una ricerca. Ad esempio, A2, D10.
Lookup_heading: l'intestazione che si desidera cercare nelle intestazioni di array_tabella.
Intestazioni_tabella: Riferimento delle intestazioni nell'array della tabella. Per esempio. se la tabella è A2, D10 e le intestazioni nella parte superiore di ogni colonna, allora è A1: D1.
Quindi, ora sappiamo di cosa abbiamo bisogno per col_index dinamico, chiariamo tutto con un esempio.
Esempio di CERCA.VERT dinamico
Per questo esempio, abbiamo questa tabella che contiene i dati degli studenti nell'intervallo A4:E16.
Usando il roll no e l'intestazione, voglio recuperare i dati da questa tabella. Per questo esempio, nella cella H4, voglio ottenere i dati del roll no scritto nella cella G4 e dell'intestazione in H3. Se cambio l'intestazione, i dati del rispettivo intervallo dovrebbero essere recuperati nella cella H4.
Scrivi questa formula nella cella H4
=CERCA.VERT(G4,B4:E16,CONFRONTA(H3,B3:E3,0),0)
Poiché il nostro array di tabelle è B4:E16, il nostro array di intestazioni diventa B3:E3.
Nota: Se i tuoi dati sono ben strutturati, le intestazioni delle colonne avranno lo stesso numero di colonne ed è la prima riga della tabella.
Come funziona:
Quindi, la parte principale sta valutando automaticamente il numero di indice della colonna. Per fare ciò, abbiamo utilizzato la funzione CONFRONTA.
CONFRONTA(H3,B3:E3,0): Poiché H3 contiene "studente", CONFRONTA restituirà 2. Se H3 avesse "Voto", avrebbe restituito 4 e così via. La formula CERCA.VERT avrà finalmente il suo col_index_num.
=CERCA.VERT(G4,B4:E16,2,0)
Come sappiamo, la funzione CONFRONTA restituisce il numero di indice di un dato valore nell'intervallo unidimensionale fornito. Quindi, CONFRONTA cercherà qualsiasi valore scritto in H3 nell'intervallo B3: E3 e restituirà il suo numero di indice.
Ora ogni volta che cambierai l'intestazione in H3, se è nelle intestazioni, questa formula restituirà un valore dalla rispettiva colonna. Altrimenti, avrai un errore #N/A.
CERCA.VERT in più colonne rapidamente
Nell'esempio sopra, avevamo bisogno della risposta da un valore di colonna. Ma cosa succede se si desidera ottenere più colonne contemporaneamente. Se copi la formula sopra, restituirà errori. Abbiamo bisogno di fare alcune piccole modifiche per renderlo portabile.
Utilizzo di riferimenti assoluti con CERCA.VERT
Scrivi sotto la formula nella cella H2.
=CERCA.VERT($G2,$B$2:$E$14,CONFRONTA(H$1,$B$1:$E$1,0),0)
Ora copia H2 in tutte le celle nell'intervallo H2: J6 per riempirlo con i dati.
Come funziona:
Qui ho dato riferimento assoluto di ogni intervallo tranne la riga nel valore di ricerca per CERCA.VERT ($G2) e colonna in lookup_value per MATCH (H$ 1).
$G2: Ciò consentirà alla riga di cambiare il valore di ricerca per la funzione CERCA.VERT durante la copia verso il basso, ma limita la modifica della colonna quando viene copiata a destra. Ciò farà sì che VLOOKUP cercherà l'ID dalla colonna G solo con la riga relativa.
Allo stesso modo, H$ 1 consentirà alla colonna di cambiare quando viene copiata orizzontalmente e restringerà la riga quando viene copiata verso il basso.
Utilizzo di intervalli denominati
L'esempio sopra funziona bene ma diventa difficile leggere e scrivere questa formula. E questo non è affatto portatile. Questo può essere semplificato usando intervalli denominati.
Faremo prima alcuni nomi qui. Per questo esempio, ho chiamato
$B$2:$E$14 : come dati
$B$1:$E$1 : come titoli
H$1 : Chiamalo come Intestazione. Rendi le colonne relative. Per farlo, seleziona H1. Premi CTRL + F3, fai clic su nuovo, nella sezione Riferimenti a rimuovere "$" dalla parte anteriore di H.
$G2: Allo stesso modo, chiamalo come RollNo. Questa volta rende la riga relativa rimuovendo '$' dalla parte anteriore di 2.
Ora, quando hai tutti i nomi sul foglio, scrivi questa formula ovunque sul file excel. Otterrà sempre la risposta corretta.
=CERCA.VERT(NumeroRoll,Dati, CONFRONTA(Titolo, Titoli,0),0)
Vedi, chiunque può leggerlo e capirlo.
Quindi, usando questi metodi, puoi rendere dinamico col_index_num. Fammi sapere se questo è stato utile nella sezione commenti qui sotto.
Come usare tla funzione CERCA.VERT in Excel
Riferimento relativo e assoluto in Excel
Intervalli denominati in Excel
Come eseguire il VLOOKUP da un diverso foglio Excel
CERCA.VERT Valori multipli
Articoli popolari
50 scorciatoie Excel per aumentare la produttività : Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come usare tla funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
