image

In link: / formule-e-funzioni-introduzione-di-vlookup-funzione [la funzione CERCA.VERT], spesso definiamo col_index_no statico. Lo codifichiamo all’interno della formula CERCA.VERT, come CERCA.VERT (id, dati, 3,0). Il problema sorge quando inseriamo o cancelliamo una colonna all’interno dei dati. Se rimuoviamo o aggiungiamo una colonna prima o dopo la terza colonna, la terza colonna non farà più riferimento alla colonna desiderata. Questo è un problema. Altro è quando hai più colonne da cercare. Dovrai modificare l’indice della colonna in ciascuna formula. Il semplice copia-incolla non aiuta.

Ma che ne dici, se puoi dire a CERCA.VERT di guardare le intestazioni e restituire solo il valore delle intestazioni corrispondenti. Questo è chiamato CERCA.VERT a due vie.

Ad esempio, se ho una formula CERCA.VERT per la colonna dei segni, CERCA.VERT dovrebbe cercare la colonna dei segni nei dati e restituire il valore da quella colonna. Questo risolverà il nostro problema.

Hmm …​ Okay, quindi come lo facciamo? Usando`link: / lookup-formulas-excel-match-function [la funzione Match] all’interno del`link: / formule-e-funzioni-introduzione-di-vlookup-funzione [VLOOKUP-funzione].

Formula generica
=VLOOKUP(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)

Lookup_value: il valore di ricerca nella prima colonna di table_array.

Table_array: l’intervallo in cui si desidera eseguire una ricerca. Ad esempio, A2, D10.

Lookup_heading: l’intestazione che vuoi cercare nelle intestazioni table_array.

Table_headings: riferimento delle intestazioni nella matrice della tabella. Per esempio. se la tabella è A2, D10 e le intestazioni all’inizio di ogni colonna, allora è A1: D1.

image

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.

Utilizzando roll no e intestazione, desidero recuperare i dati da questa tabella. Per questo esempio, nella cella H4, voglio ottenere i dati del rotolo non scritto nella cella G4 e dell’intestazione in H3. Se cambio l’intestazione, i dati dal rispettivo intervallo dovrebbero essere recuperati nella cella H4.

Scrivi questa formula nella cella H4

=VLOOKUP(G4,B4:E16,MATCH(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 di colonna avranno lo stesso numero di colonne ed è la prima riga della tabella.

image

Come funziona:

Quindi, la parte principale sta valutando automaticamente il numero di indice della colonna.

Per fare ciò, abbiamo utilizzato la funzione CONFRONTA.

MATCH (H3, B3: E3,0): Dato che H3 contiene “studente”, MATCH tornerà 2.

Se H3 avesse “Grade”, sarebbe tornato 4, e così via. La formula CERCA.VERT avrà finalmente col_index_num.

=VLOOKUP(G4,B4:E16,2,0)

Come sappiamo, il`link: / lookup-formas-excel-match-function [MATCH] `

restituisce il numero di indice di un dato valore nell’intervallo unidimensionale fornito. Quindi, link: / lookup-formulas-excel-match-function [MATCH] 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. In caso contrario, verrà visualizzato un errore # N / A.

VLOOKUP 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. Dobbiamo apportare alcune piccole modifiche per renderlo portatile.

image

Utilizzando link: / excel-range-name-absolute-reference-in-excel [Absolute References] con CERCA.VERT Scrivi sotto la formula nella cella H2.

=VLOOKUP($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)

Ora copia H2 in tutte le celle nell’intervallo H2: J6 per riempirlo con i dati.

image

Come funziona:

Qui ho dato link: / nome-intervallo-excel-riferimento-assoluto-in-excel [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 limiterà la modifica della colonna quando viene copiata a destra. Ciò farà sì che CERCA.VERT cerchi 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 con nome

L’esempio sopra funziona bene ma diventa difficile leggere e scrivere questa formula. E questo non è affatto portatile. Ciò può essere semplificato utilizzando intervalli denominati.

Faremo prima un po ‘di denominazione qui. Per questo esempio, ho chiamato $ B $ 2: $ E $ 14: come Dati $ B $ 1: $ E $ 1: come Intestazioni H $ 1: Nome come Intestazione. Rendi relative le colonne. Per fare ciò, seleziona H1. Premere CTRL + F3, fare clic su nuovo, nella sezione Riferito a rimuovere ‘$’ dalla parte anteriore di H.

image

$ G2: Allo stesso modo, chiamalo RollNo. Questa volta rende relativa la riga 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.

=VLOOKUP(RollNo,Data, MATCH(Heading, Headings,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.

Articoli correlati:

link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT in Excel] collegamento:% 20 e% 20Riferimento assoluto% 20% 20Excel [Riferimento relativo e assoluto in Excel]

link: / excel-range-name-all-about-named-range-in-excel [Intervalli con nome in Excel]

link: / lookup-formulas-how-to-vlookup-from-different-excel-sheet [Come fare CERCA.VERT da un foglio Excel diverso]

link: / lookup-formas-vlookup-multiple-values ​​[VLOOKUP Valori multipli]

Articoli popolari

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoia Excel per aumentare la produttività]: Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.

link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT in Excel]: Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da intervalli diversi e lenzuola.

link: / tips-countif-in-microsoft-excel [Come usare la funzione CONTA.SE in Excel]: Conta i valori con le condizioni usando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.

link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel]: Questa è un’altra funzione essenziale del dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.