Come utilizzare la funzione XLOOKUP di Excel -

Sommario

La funzione XLOOKUP di Excel è un sostituto moderno e flessibile per funzioni precedenti come CERCA.VERT, CERCA.ORIZZ e CERCA. XLOOKUP supporta corrispondenze approssimative ed esatte, caratteri jolly (*?) Per corrispondenze parziali e ricerche in intervalli verticali o orizzontali.

Scopo

Valori di ricerca nell'intervallo o nella matrice

Valore di ritorno

Valori corrispondenti dall'array di ritorno

Sintassi

= XLOOKUP (lookup, lookup_array, return_array, (not_found), (match_mode), (search_mode))

argomenti

  • lookup : il valore di ricerca.
  • lookup_array - La matrice o l'intervallo in cui cercare.
  • return_array - La matrice o l'intervallo da restituire.
  • not_found - (opzionale) Valore da restituire se non viene trovata alcuna corrispondenza.
  • match_mode - (opzionale) 0 = corrispondenza esatta (impostazione predefinita), -1 = corrispondenza esatta o successiva più piccola, 1 = corrispondenza esatta o successiva più grande, 2 = corrispondenza con caratteri jolly.
  • modalità_ricerca - (opzionale) 1 = ricerca dalla prima (impostazione predefinita), -1 = ricerca dall'ultima, 2 = ricerca binaria crescente, -2 = ricerca binaria discendente.

Versione

Excel 365

Note sull'utilizzo

XLOOKUP è un moderno sostituto della funzione CERCA.VERT. È una funzione flessibile e versatile che può essere utilizzata in un'ampia varietà di situazioni.

XLOOKUP può trovare valori in intervalli verticali o orizzontali, può eseguire corrispondenze approssimative ed esatte e supporta i caratteri jolly (*?) Per le corrispondenze parziali. Inoltre, XLOOKUP può cercare dati a partire dal primo o dall'ultimo valore (vedere i dettagli del tipo di corrispondenza e della modalità di ricerca di seguito). Rispetto alle funzioni precedenti come CERCA.ORIZZ, CERCA.ORIZZ e CERCA, XLOOKUP offre diversi vantaggi chiave.

Messaggio non trovato

Quando XLOOKUP non riesce a trovare una corrispondenza, restituisce l'errore # N / D, come altre funzioni di corrispondenza in Excel. A differenza delle altre funzioni di corrispondenza, XLOOKUP supporta un argomento facoltativo chiamato not_found che può essere utilizzato per ignorare l'errore # N / A quando altrimenti apparirebbe. I valori tipici per not_found potrebbero essere "Not found", "No match", "No result", ecc. Quando si fornisce un valore per not_found, racchiudere il testo tra virgolette doppie ("").

Nota: fai attenzione se fornisci una stringa vuota ("") per not_found. Se non viene trovata alcuna corrispondenza, XLOOKUP non mostrerà nulla invece di # N / A. Se vuoi vedere l'errore # N / A quando non viene trovata una corrispondenza, ometti completamente l'argomento.

Tipo di corrispondenza

Per impostazione predefinita, XLOOKUP eseguirà una corrispondenza esatta. Il comportamento della corrispondenza è controllato da un argomento opzionale chiamato match_type, che ha le seguenti opzioni:

Tipo di corrispondenza Comportamento
0 (predefinito) Corrispondenza esatta. Restituirà # N / A se non corrisponde.
-1 Corrispondenza esatta o elemento successivo più piccolo.
1 Corrispondenza esatta o elemento successivo più grande.
2 Corrispondenza con caratteri jolly (*,?, ~)

Modalità di ricerca

Per impostazione predefinita, XLOOKUP inizierà la corrispondenza dal primo valore di dati. Il comportamento della ricerca è controllato da un argomento opzionale chiamato search_mode , che fornisce le seguenti opzioni:

Modalità di ricerca Comportamento
1 (predefinito) Cerca dal primo valore
-1 Cerca dall'ultimo valore (inverso)
2 Valori di ricerca binaria ordinati in ordine crescente
-2 Valori di ricerca binaria ordinati in ordine decrescente

Le ricerche binarie sono molto veloci, ma i dati devono essere ordinati secondo necessità. Se i dati non vengono ordinati correttamente, una ricerca binaria può restituire risultati non validi che sembrano perfettamente normali.

Esempio n. 1: corrispondenza esatta di base

Per impostazione predefinita, XLOOKUP eseguirà una corrispondenza esatta. Nell'esempio seguente, XLOOKUP viene utilizzato per recuperare le vendite in base a una corrispondenza esatta su Film. La formula in H5 è:

=XLOOKUP(H4,B5:B9,E5:E9)

Spiegazione più dettagliata qui.

Esempio # 2 - corrispondenza approssimativa di base

Per abilitare una corrispondenza approssimativa, fornire un valore per l'argomento "match_mode". Nell'esempio seguente, XLOOKUP viene utilizzato per calcolare uno sconto in base alla quantità, che richiede una corrispondenza approssimativa. La formula in F5 fornisce -1 per match_mode per abilitare la corrispondenza approssimativa con il comportamento "corrispondenza esatta o successiva più piccola":

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Spiegazione più dettagliata qui.

Esempio n. 3: più valori

XLOOKUP può restituire più di un valore contemporaneamente per la stessa corrispondenza. L'esempio seguente mostra come XLOOKUP può essere configurato per restituire tre valori di corrispondenza con una singola formula. La formula in C5 è:

=XLOOKUP(B5,B8:B15,C8:E15)

Notare che l'array di ritorno (C8: E15) include 3 colonne: First, Last, Department. Tutti e tre i valori vengono restituiti e si estendono nell'intervallo C5: E5.

Esempio n. 4: ricerca a due vie

XLOOKUP può essere utilizzato per eseguire una ricerca a due vie, annidando un XLOOKUP all'interno di un altro. Nell'esempio seguente, XLOOKUP "interno" recupera un'intera riga (tutti i valori per Glass), che viene passata a XLOOKUP "esterno" come matrice di ritorno. XLOOKUP esterno trova il gruppo appropriato (B) e restituisce il valore corrispondente (17.25) come risultato finale.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Maggiori dettagli qui.

Esempio # 5 - messaggio non trovato

Come altre funzioni di ricerca, se XLOOKUP non trova un valore, restituisce l'errore # N / D. Per visualizzare un messaggio personalizzato invece di # N / D, fornire un valore per l'argomento facoltativo "non trovato", racchiuso tra virgolette doppie (""). Ad esempio, per visualizzare "Non trovato" quando non viene trovato alcun film corrispondente, in base al foglio di lavoro seguente, utilizza:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Puoi personalizzare questo messaggio come preferisci: "Nessuna corrispondenza", "Film non trovato", ecc.

Esempio # 6 - criteri complessi

Grazie alla capacità di gestire gli array in modo nativo, XLOOKUP può essere utilizzato con criteri complessi. Nell'esempio seguente, XLOOKUP corrisponde al primo record dove: l'account inizia con "x" e la regione è "est" e il mese non è aprile:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Dettagli: (1) esempio semplice, (2) esempio più complesso.

Vantaggi di XLOOKUP

XLOOKUP offre diversi importanti vantaggi, soprattutto rispetto a CERCA.VERT:

  • XLOOKUP può cercare i dati a destra oa sinistra dei valori di ricerca
  • XLOOKUP può restituire più risultati (esempio n. 3 sopra)
  • XLOOKUP ha come impostazione predefinita una corrispondenza esatta (VLOOKUP è impostato come predefinito approssimativo)
  • XLOOKUP può funzionare con dati verticali e orizzontali
  • XLOOKUP può eseguire una ricerca inversa (dall'ultimo al primo)
  • XLOOKUP può restituire intere righe o colonne, non solo un valore
  • XLOOKUP può lavorare con gli array in modo nativo per applicare criteri complessi

Appunti

  1. XLOOKUP può funzionare con array sia verticali che orizzontali.
  2. XLOOKUP restituirà # N / D se il valore di ricerca non viene trovato.
  3. Il matrice_ricerca deve avere una dimensione compatibile con il return_array argomento, altrimenti XLOOKUP tornerà # valore!
  4. Se XLOOKUP viene utilizzato tra le cartelle di lavoro, entrambe le cartelle di lavoro devono essere aperte, altrimenti XLOOKUP restituirà #REF !.
  5. Come la funzione INDICE, XLOOKUP restituisce come risultato un riferimento.

Video collegati

Esempio di XLOOKUP di base In questo video, imposteremo la funzione XLOOKUP con un esempio di base. Corrispondendo al nome della città, recupereremo Paese e popolazione. Corrispondenza approssimativa XLOOKUP di base In questo video, imposteremo la funzione XLOOKUP per eseguire una corrispondenza approssimativa al fine di calcolare uno sconto basato sulla quantità. XLOOKUP con logica booleana In questo video vedremo come utilizzare la funzione XLOOKUP con logica booleana per applicare più criteri. XLOOKUP con più valori di ricerca In questo video, imposteremo XLOOKUP per restituire più valori in un array dinamico, fornendo un intervallo di valori di ricerca invece di un singolo valore di ricerca.

Articoli interessanti...