Cómo funciona.
BUSCARX es una función fundamental de Excel. Busca un valor en un rango o matriz y devuelve el elemento correspondiente de un segundo rango o matriz. Permite automatizar hojas de cálculo, simplificar fórmulas complejas y analizar datos con total precisión en Microsoft Excel y Google Sheets.
💡 En palabras sencillas.
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)."
Sintaxis.
Notación Estándar de Excel=BUSCARX(valor_buscado; matriz_búsqueda; matriz_devuelta; [si_no_se_encuentra]; [modo_coincidencia])Argumentos.
A continuación se detallan todos los parámetros aceptados por esta función.
The value you want to search for.
Ejemplo:D2The range or array to search in.
Ejemplo:A2:A6The range or array to return values from.
Ejemplo:B2:B6Text or value to return if no match is found.
Valor por defecto: "Not Found"0 = Exact match (default), -1 = Exact or next smaller, 1 = Exact or next larger, 2 = Wildcard match.
Valor por defecto: 01 = Search first-to-last (default), -1 = Search last-to-first, 2 = Binary search (sorted asc), -2 = Binary search (sorted desc).
Valor por defecto: 1Ejemplo práctico.
Caso de Uso Paso a PasoDepartment 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.
Generador de fórmula.
Abrir Asistente CompletoPersonaliza los campos de argumento a continuación para generar una fórmula lista para copiar y pegar en tu hoja de cálculo.
=BUSCARX()Comportamiento del resultado.
Returns "Sales" based on sample evaluation criteria and active cell references.
Buenas Prácticas
- 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.
Errores Frecuentes
- 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.
Compatibilidad de plataforma y versiones.
Matriz verificada con base en los motores de cálculo oficiales de Microsoft Excel y Google Sheets.
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.
Preguntas frecuentes.
Dudas TécnicasCan 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.