Come funziona nel dettaglio.
CERCA.X è una funzione fondamentale di Excel. Cerca un valore in un intervallo o matrice e restituisce l'elemento corrispondente da un secondo intervallo. Consente di velocizzare la gestione dei fogli di calcolo, ridurre gli errori e creare report automatizzati professionali.
💡 In parole semplici.
Searches for a value in one list (lookup array) and returns matching information from another list (return array) in the exact same row.
"Think of XLOOKUP like looking up a word in a dictionary. You have a search word (Employee ID), you scan the dictionary list (ID Column), and you get the corresponding definition (Department Column)."
Sintassi.
Notazione Ufficiale Excel=CERCA.X(valore; matrice_ricerca; matrice_restituita; [se_non_trovato]; [modalità_confronto])Argomenti.
Di seguito l'elenco dettagliato di tutti i parametri accettati da questa funzione.
The value you want to search for.
Valore d'esempio:D2The range or array to search in.
Valore d'esempio:A2:A6The range or array to return values from.
Valore d'esempio:B2:B6Text or value to return if no match is found.
Valore predefinito: "Not Found"0 = Exact match (default), -1 = Exact or next smaller, 1 = Exact or next larger, 2 = Wildcard match.
Valore predefinito: 01 = Search first-to-last (default), -1 = Search last-to-first, 2 = Binary search (sorted asc), -2 = Binary search (sorted desc).
Valore predefinito: 1Esempio pratico.
Scenario di utilizzoDepartment Directory Lookup
Retrieve an employee's Department by searching their Employee ID.
| Emp ID | Department | Salary | Search ID | Result Dept |
|---|---|---|---|---|
| 101 | Engineering | 105000 | 103 | — |
| 102 | Marketing | 88000 | — | — |
| 103 | Sales | 95000 | — | — |
=XLOOKUP(D2, A2:A6, B2:B6, "Not Found")Evaluates XLOOKUP over the sample table cells, placing the verified output in cell E2.
Generatore di formula.
Apri Procedura Guidata CompletaCompila i campi qui sotto per creare una formula personalizzata pronta da incollare nel tuo foglio di calcolo.
=CERCA.X()Caratteristiche del risultato.
Returns "Sales" based on sample evaluation criteria and active cell references.
Migliori Pratiche
- Use XLOOKUP to return multiple items simultaneously by selecting a multi-column return range.
- Unlike VLOOKUP, XLOOKUP does not break when columns are inserted or deleted in the spreadsheet.
- Leave match_mode blank if you want an exact match, which is what is needed 95% of the time.
Errori Più Frequenti
- XLOOKUP is only available in Excel 365, Excel 2021, and Excel for the Web. It will return a #NAME? error in older versions.
- Ensure the lookup_array and return_array are of equal length; otherwise, XLOOKUP returns a #VALUE! error.
Compatibilità per programma e versione.
Verifica tecnica eseguita sui motori di calcolo ufficiali di Microsoft Excel e Google Fogli.
In Excel 2019 or legacy versions, use INDEX & MATCH (=INDEX(return_range, MATCH(lookup_val, lookup_range, 0))) which works in all versions.
Introduced in Excel 365 and Excel 2021. Not supported in Excel 2019 or Excel 2016 (#NAME? error).
Fully supported in Google Sheets with identical parameters, default exact match, and search modes.
Domande frequenti.
Domande TecnicheCan XLOOKUP look up values to the left?↓
Yes. Unlike VLOOKUP which requires the return column to be to the right of the lookup column, XLOOKUP uses separate lookup_array and return_array ranges, allowing you to return data located in any column or row.
What is the primary difference between XLOOKUP and VLOOKUP?↓
XLOOKUP defaults to an exact match (VLOOKUP defaults to approximate match), does not break when columns are inserted into the table, handles missing values gracefully with the if_not_found parameter, and supports both vertical and horizontal searches.