La funzione XLOOKUP è esclusiva del programma insider di Office 365.

La funzione CERCA ha molte funzionalità che superano molti dei punti deboli della funzione CERCA.VERT e CERCA.ORIZZ, ma purtroppo non è disponibile per ora. Ma non preoccuparti, possiamo creare una funzione XLOOKUP che funziona esattamente come la prossima funzione XLOOKUP MS Excel. Aggiungeremo funzionalità ad esso una per una.

Codice VBA della funzione XLOOKUP La funzione di ricerca UDF sottostante risolverà molti problemi. Copialo o scarica il componente aggiuntivo xl sotto il file di seguito.

Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range)

XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0))

End Function

Spiegazione:

Il codice sopra è solo INDEX-MATCH di base utilizzato in VBA. Questo semplifica molte delle cose che un nuovo utente deve affrontare. If risolve la complessità della funzione INDICE-CONFRONTA e utilizza solo tre argomenti. Puoi copiarlo nel tuo file excel o scaricare il file .xlam di seguito e installarlo come componente aggiuntivo di Excel. Se non sai come creare e usare un add-in, `link: / excel-macros-and-vba-add-in-in-vb [fai clic qui, ti aiuterà].

image 48XLOOKUP Add-In]

vediamo come funziona sul foglio di lavoro Excel.

Sintassi di XLOOKUP

=XLOOKUP(lookup_value, lookup_array, result_array)

lookup_value: questo è il valore che vuoi cercare nel

lookup_array.

lookup_array: è un intervallo unidimensionale in cui si desidera cercare lookup_value. array_risultato: è anche un intervallo unidimensionale. Questo è l’intervallo da cui vuoi recuperare il valore.

Vediamo questa funzione XLOOKUP in azione.

XLOOKUP Esempi:

image

Qui, ho una tabella di dati in Excel. Esploriamo alcune funzionalità utilizzando questa tabella dati.

Funzionalità 1.ExactLookup sul lato sinistro e destro del valore di ricerca. Come sappiamo che la funzione CERCA.VERT di Excel non può recuperare i valori dalla sinistra del valore di ricerca. Per questo, devi usare la complessa combinazione INDICE-PARTITA. Ma non più.

image

Supponendo che abbiamo bisogno di recuperare tutte le informazioni disponibili nella tabella di alcuni numeri di rotolo. In tal caso, dovrai recuperare anche la regione, che si trova a sinistra della colonna del numero del rotolo.

Scrivi questa formula, I2:

=XLOOKUP(H2,$B$2:$B$14,$A$2:$A$14)

Otteniamo il risultato Nord per il rotolo numero 112. Copia o trascina verso il basso la formula nelle celle sottostanti per riempirle con le rispettive regioni.

Come funziona?

Il meccanismo è semplice. Questa funzione cerca lookup_value in lookup_array e restituisce l’indice di una prima corrispondenza esatta. Quindi usa quell’indice per recuperare il valore da result_array. Questa funzione funziona perfettamente con intervalli denominati.

Allo stesso modo, utilizza questa formula per recuperare il valore da ciascuna colonna.

image

Funzionalità 2. ExactHorizontalLookup sopra e sotto il valore di ricerca. XLOOKUP funziona anche come una esatta funzione CERCA.ORIZZ. La funzione CERCA.ORIZZ ha le stesse limitazioni di CERCA.ORIZZ. Non può recuperare il valore da sopra il valore di ricerca. Ma XLOOKUP non funziona solo come HLOOKUP, ma supera anche quella debolezza. Vediamo come.

Ipoteticamente, se vuoi confrontare due record. Il record di ricerca che hai già. Il record con cui eseguire il confronto è al di sopra di lookup_range. Usa questa formula in questo caso.

=XLOOKUP(H7,$A$9:$E$9,$A$2:$E$2)

trascina verso il basso la formula e hai l’intero record della riga di confronto.

image

Funzionalità 3. Non è necessario il numero di colonna e la corrispondenza esatta predefinita.

Quando si utilizza la funzione CERCA.VERT, è necessario indicare il numero di colonna da cui si desidera recuperare i valori. Per questo, devi contare le colonne o usare alcuni trucchi, prendere l’aiuto di altre funzioni. Con questo XLOOKUP UDF, non è necessario farlo.

Se stai utilizzando CERCA.VERT solo per recuperare un valore da una colonna o per verificare se esiste un valore nella colonna, questa è la soluzione migliore secondo me.

Funzionalità 4. Sostituisce la funzione INDEX-MATCH, CERCA.VERT, CERCA.ORIZZ.

Per compiti semplici, la nostra funzione XLOOKUP sostituisce le funzioni sopra menzionate.

Limitazioni di XLOOKUP:

Quando si tratta di formule complesse, come //lookup-formulas/vlookup-with-dynamic-col-index.html[VLOOKUP with Dynamic Col Index] `dove CERCA.VERT identifica la colonna di ricerca con le intestazioni, questo XLOOKUP fallirà.

Un altro limite è che se devi cercare più colonne o righe casuali dalla tabella, questa funzione sarà inutile poiché dovrai scrivere questa formula ancora e ancora. Questo può essere superato usando link: / nome-intervallo-excel-all-about-nome-intervallo-excel-nome-intervallo-excel [intervalli con nome].

Per ora, non abbiamo aggiunto la funzionalità approssimativa, quindi ovviamente non è possibile ottenere la corrispondenza approssimativa. Lo aggiungeremo troppo presto.

Se la funzione XLOOKUP non riesce a trovare il valore di ricerca, restituirà l’errore #VALUE non # N / A

Quindi sì ragazzi, è così che usi XLOOKUP per recuperare, cercare e convalidare i valori nelle tabelle Excel. È possibile utilizzare questa funzione definita dall’utente per una ricerca senza problemi a sinistra o in alto rispetto al valore di ricerca. Se hai ancora qualche dubbio o qualsiasi requisito specifico relativo a questa funzione o query relativa a EXCEL 2010/2013/2016/2019/365 o VBA, chiedilo nella sezione commenti qui sotto. Riceverai sicuramente una risposta.

Articoli correlati:

link: / custom-functions-in-vba-create-vba-function-to-return-array [Crea funzione VBA per restituire array] | Per restituire un array dalla funzione definita dall’utente, dobbiamo dichiararlo quando nominiamo l’UDF.

link: / excel-array-formas-arrays-in-excel-formula [Arrays in Excel Formul] | Scopri cosa sono gli array in Excel.

link: / vba-user-defined-function [Come creare una funzione definita dall’utente tramite VBA] | Impara come creare funzioni definite dall’utente in Excel `link: / custom-functions-userdefined-functions-from-other-workbooks-using-vba-in-microsoft-excel [Using a User Defined Function (UDF) from another workbook using VBA in Microsoft Excel] `| Utilizzare la funzione definita dall’utente in un’altra cartella di lavoro di Excel `link: / funzioni-personalizzate-restituire-valori-errore-da-funzioni-definite-dall’utente-using-vba-in-microsoft-excel [Restituire valori di errore da definiti dall’utente funzioni utilizzando VBA in Microsoft Excel] `| Scopri come restituire valori di errore da una funzione definita dall’utente

Articoli popolari:

`link: / general-topics-in-vba-split-excel-sheet-into-multiple-files-based-on-column-using-vba [Dividi il foglio Excel in più file in base alla colonna usando VBA] Questo codice VBA ha suddiviso la base del foglio Excel su valori univoci in una colonna specificata.

Scarica il file di lavoro.

link: / general-topics-in-vba-turn-off-warning-messages-using-vba-in-microsoft-excel [Disattiva i messaggi di avviso usando VBA in Microsoft Excel 2016] | Per disattivare i messaggi di avviso che interrompono il codice VBA in esecuzione, utilizziamo la classe Application.

link: / files-workbook-and-worksheets-in-vba-add-and-save-new-workbook-using-vba-in-microsoft-excel [Aggiungi e salva una nuova cartella di lavoro usando VBA in Microsoft Excel 2016] | Per aggiungere e salvare cartelle di lavoro utilizzando VBA utilizziamo la classe Cartelle di lavoro. Workbooks.Add aggiunge facilmente una nuova cartella di lavoro, tuttavia …​