¿Sabías que Excel tiene una nueva función llamada XMATCH que puede cambiar la forma en que realizas búsquedas y coincidencias de datos? En este artículo, te mostraremos qué es la función XMATCH de Excel y cómo puedes utilizarla en tus fórmulas. Aprenderás cómo encontrar el valor más cercano en un rango, cómo buscar coincidencias en columnas o filas y mucho más. ¡No te lo pierdas! Sigue leyendo para descubrir cómo la función XMATCH de Excel puede ser tu nueva herramienta favorita en el mundo de las fórmulas.
El tutorial presenta la nueva función XMATCH de Excel y muestra cómo es mejor que MATCH para resolver algunas tareas comunes.
En Excel 365, se agregó la función XMATCH para reemplazar la función MATCH. Pero antes de comenzar a actualizar sus fórmulas existentes, sería prudente comprender todas las ventajas de la nueva función y en qué se diferencia de la anterior.
En resumen, la función XMATCH es igual que MATCH pero más flexible y robusta. Puede buscar en matrices verticales y horizontales, buscar del primero al último o del último al primero, encontrar coincidencias exactas, aproximadas y parciales y utilizar un algoritmo de búsqueda binaria más rápido.
Función XMATCH de Excel
La función XMATCH en Excel devuelve la posición relativa de un valor en una matriz o rango de celdas.
Tiene la siguiente sintaxis:
XMATCH(valor_buscado, matriz_buscada, [match_mode], [search_mode])
Dónde:
Valor de búsqueda (obligatorio): el valor a buscar.
matriz_de_búsqueda (obligatorio): la matriz o rango de celdas donde buscar.
Modo_partido (opcional): especifica qué tipo de coincidencia utilizar:
- 0 u omitido (predeterminado): coincidencia exacta
- -1 – coincidencia exacta o el siguiente valor más pequeño
- 1: coincidencia exacta o el siguiente valor más grande
- 2 – coincidencia con comodín (*, ?)
Modo de búsqueda (opcional): especifica la dirección de búsqueda y el algoritmo:
- 1 u omitido (predeterminado): busca del primero al último.
- -1 – busca en orden inverso del último al primero.
- 2 – búsqueda binaria ascendente. Requiere matriz_buscada ordenarse en orden ascendente.
- -2 – búsqueda binaria descendente. Requiere matriz_buscada ordenarse en orden descendente.
La búsqueda binaria es un algoritmo más rápido que funciona de manera eficiente en matrices ordenadas. Para obtener más información, consulte Modo de búsqueda.
¿Qué versión de Excel tiene XMATCH?
La función XMATCH solo está disponible en Excel para Microsoft 365 y Excel 2021. En Excel 2019, Excel 2016 y versiones anteriores, esta función no es compatible.
Fórmula XMATCH básica en Excel
Para tener una idea general de lo que la función es capaz de hacer, construyamos una fórmula XMATCH en su forma más simple, definiendo solo los dos primeros argumentos requeridos y dejando los opcionales con sus valores predeterminados.
Supongamos que tiene una lista de océanos clasificados por su tamaño (C2:C6) y desea encontrar la clasificación de un océano en particular. Para hacerlo, simplemente use el nombre del océano, digamos indiocomo valor de búsqueda y la lista completa de nombres como matriz de búsqueda:
=XMATCH("Indian", C2:C6)
Para hacer la fórmula más flexible, ingrese el océano de interés en alguna celda, digamos F1:
=XMATCH(F1, C2:C6)
Como resultado, obtienes una fórmula XMATCH para buscar en un matriz vertical. El resultado es la posición relativa del valor de búsqueda en la matriz, que en nuestro caso corresponde al rango del océano:
Una fórmula similar funciona perfectamente para un matriz horizontal también. Todo lo que necesitas hacer es ajustar el matriz_buscada referencia:
=XMATCH(B5, B1:F1)
Función XMATCH de Excel: cosas para recordar
Para utilizar XMATCH de forma eficaz en sus hojas de trabajo y evitar resultados inesperados, recuerde estos 3 hechos simples:
- Si hay dos o más apariciones del valor de búsqueda en la matriz de búsqueda, la posición del primer partido se devuelve si el modo de búsqueda El argumento se establece en 1 o se omite. Con modo de búsqueda establecido en -1, la función busca en orden inverso y devuelve la posición del el ultimo partido como se demuestra en este ejemplo.
- Si el valor de búsqueda no se encuentrase produce un error #N/A.
- La función XMATCH es no distingue entre mayúsculas y minúsculas por naturaleza y no puede distinguir las letras entre mayúsculas y minúsculas. Para distinguir caracteres en minúsculas y mayúsculas, utilice este fórmula XMATCH que distingue entre mayúsculas y minúsculas.
Cómo usar XMATCH en Excel: ejemplos de fórmulas
Los siguientes ejemplos le ayudarán a comprender mejor la función XMATCH y sus usos prácticos.
Coincidencia exacta versus coincidencia aproximada
El comportamiento de coincidencia de XMATCH está controlado por el opcional modo_partido argumento:
- 0 u omitido (predeterminado): la fórmula solo busca coincidencias exactas. Si no se encuentra una coincidencia exacta, se devuelve un error #N/A.
- -1: la fórmula busca primero la coincidencia exacta y luego el siguiente elemento más pequeño.
- 1: la fórmula busca primero la coincidencia exacta y luego el siguiente elemento más grande.
Y ahora, veamos cómo los diferentes modos de coincidencia afectan el resultado de la fórmula. Supongamos que desea saber dónde se encuentra un área determinada, digamos 80.000.000 km.2se encuentra entre todos los océanos.
Coincidencia exacta
Si usas 0 para modo_partidoobtendrá un error #N/A, porque la fórmula no puede encontrar un valor exactamente igual al valor de búsqueda:
=XMATCH(80000000, C2:C6, 0)
Siguiente elemento más pequeño
Si usas -1 para modo_partidola fórmula devolverá 3, porque la coincidencia más cercana menor que el valor de búsqueda es 70.560.000, y es el 3tercero elemento en la matriz de búsqueda:
=XMATCH(80000000, C2:C6, -1)
Siguiente artículo más grande
Si usas 1 para modo_partidola fórmula generará 2, porque la coincidencia más cercana mayor que el valor de búsqueda es 85.133.000, que es el 2Dakota del Norte elemento en la matriz de búsqueda:
=XMATCH(80000000, C2:C6, -1)
La siguiente imagen muestra todos los resultados:
Cómo hacer coincidir texto parcial en Excel con comodines
La función XMATCH tiene un modo de coincidencia especial para comodines: el modo_partido argumento establecido en 2.
En el modo de coincidencia con comodines, una fórmula XMATCH acepta los siguientes caracteres comodín:
- Signo de interrogación (?) para que coincida con cualquier carácter.
- Asterisco
para coincidir con cualquier secuencia de caracteres.
Tenga en cuenta que los comodines solo funcionan con texto, no con números.
=XMATCH("south*", B2:B6, 2)
Por ejemplo, para encontrar la posición del primer elemento que comienza con “sur”, la fórmula es: O puede escribir su expresión comodín en alguna celda, digamos F1, y proporcionar la referencia de celda para el valor de búsqueda
=XMATCH(F1, B2:B6, 2)
Buscar coincidencias de texto parciales con comodines
Con la mayoría de las funciones de Excel, usaría tilde (~) para tratar el asterisco (~*) o el signo de interrogación (~?) como caracteres literales, no comodines. Con XMATCH, no se necesita tilde. Si no define el modo de coincidencia con comodines, XMATCH asumirá que ? y * son caracteres regulares.
=XMATCH("*", A2:A7)
Haga coincidir el asterisco como un carácter normal, no comodín.
Búsqueda inversa XMATCH para encontrar la última coincidencia En caso de que haya varias apariciones del valor de búsqueda en la matriz de búsqueda, es posible que a veces necesite obtener la posición delúltima ocurrencia
.La dirección de búsqueda se controla sea el 4 th argumento de XMATCH llamadomodo de búsqueda . Para buscar en orden inverso, es decir, de abajo hacia arriba en una matriz vertical y de derecha a izquierda en una matriz horizontal, modo de búsqueda
debe establecerse en -1.
- En este ejemplo, devolveremos la posición del último registro para un valor de búsqueda específico (consulte la captura de pantalla a continuación). Para ello, configure los argumentos de la siguiente manera: Valor de búsqueda
- – el vendedor objetivo en el primer semestre matriz_de_búsqueda
- – nombres de vendedores en C2:C10 Modo_partido
- es 0 o se omite (coincidencia exacta) Modo de búsqueda
es -1 (del último al primero)
=XMATCH(H1, C2:C10, 0, -1)
Juntando los cuatro argumentos obtenemos esta fórmula:
Búsqueda inversa XMATCH para encontrar la última coincidencia
Cómo comparar dos columnas en Excel para que coincidan
Para comparar dos listas en busca de coincidencias, puede utilizar la función XMATCH junto con IF e ISNA:SI( ISNA( XMATCH(lista_objetivo ,lista de búsqueda
0)), “No coincide”, “Coincide”)
=IF(ISNA(XMATCH(B2:B10, A2:A9)), "", "Match in List 1")
Por ejemplo, para comparar la Lista 2 en B2:B10 con la Lista 1 en A2:A10, la fórmula toma la siguiente forma: En este ejemplo, identificamos sólo coincidencias, por lo tanto, el valor_si_verdadero
El argumento de la función SI es una cadena vacía (“”). Ingrese la fórmula anterior en la celda superior (C2 en nuestro caso), presioneIngresar
Comparando dos columnas para coincidencias
Cómo funciona esta fórmula
{#N/A;#N/A;2;#N/A;4;#N/A;#N/A;8;#N/A}
En el corazón de la fórmula, la función XMATCH busca un valor de la Lista 2 dentro de la Lista 1. Si se encuentra un valor, se devuelve su posición relativa; de lo contrario, se muestra un error #N/A. En nuestro caso, el resultado de XMATCH es la siguiente matriz:
{TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}
Esta matriz se “alimenta” a la función ISNA para que se verifique si hay errores #N/A. Para cada error #N/A, ISNA devuelve VERDADERO; para cualquier otro valor – FALSO. Como resultado, produce la siguiente matriz de valores lógicos, donde VERDADERO representa no coincidencias y FALSO representa coincidencias:La matriz anterior pasa a la prueba lógica de la función SI. Dependiendo de cómo haya configurado los dos últimos argumentos, la fórmula generará el texto correspondiente. En nuestro caso, es una cadena vacía (“”) para no coincidencias (valor_si_verdadero) y “Coincidencia en la Lista 1” para coincidencias (valor_si_falso
). Nota.
Esta fórmula solo funciona en Excel 365 y Excel 2021 que admiten matrices dinámicas. Si está utilizando Excel 2019, Excel 2016 o una versión anterior, consulte otras soluciones: Cómo comparar dos columnas en Excel.
ÍNDICE XMATCH en Excel
XMATCH se puede utilizar en combinación con la función INDEX para recuperar un valor de otra columna asociada con el valor de búsqueda, al igual que la fórmula INDEX MATCH. El enfoque genérico es el siguiente:ÍNDICE (devolver_formaciónXMATCH (valor de búsqueda ,matriz_buscada
)
La lógica es muy sencilla y fácil de seguir: La función XMATCH calcula la posición relativa del valor de búsqueda en la matriz de búsqueda y lo pasa al número_fila
argumento del ÍNDICE. Según el número de fila, la función ÍNDICE devuelve un valor de cualquier columna que especifique.
=INDEX(B2:B6, XMATCH(E1, A2:A6))
Fórmula ÍNDICE XMATCH en Excel
INDEX XMATCH XMATCH para realizar búsquedas bidimensionales
Para buscar en columnas y filas simultáneamente, use INDEX junto con dos funciones XMATCH. El primer XMATCH obtendrá el número de fila y el segundo recuperará el número de columna:ÍNDICE (datosXMATCH (valor de búsqueda ,vertical_matriz_buscada), XMATCH (valor de búsqueda ,horizontal_matriz_buscada
)) La fórmula es similar a INDEX MATCH MATCH excepto que puedes omitir el modo_partido
argumento ya que por defecto es una coincidencia exacta.
=INDEX(B2:D8, XMATCH(G1, A2:A8), XMATCH(G2, B1:D1))
Por ejemplo, para recuperar un número de ventas para un artículo determinado (G1) en un mes específico (G2), la fórmula es:
Fórmula INDEX XMATCH XMATCH para realizar búsquedas bidireccionales
Fórmula XMATCH que distingue entre mayúsculas y minúsculas
Como ya se mencionó, la función XMATCH de Excel no distingue entre mayúsculas y minúsculas por diseño. Para forzarlo a distinguir entre mayúsculas y minúsculas, use XMATCH en combinación con la función EXACTA:COINCIDENCIA(VERDADERO, EXACTO(matriz_buscada ,valor de búsqueda
)) para buscar en orden inverso
del último al primero:COINCIDENCIA(VERDADERO, EXACTO(matriz_buscada ,valor de búsqueda
), 0, -1)
=XMATCH(TRUE, EXACT(B2:B11, E1))
Fórmula XMATCH que distingue entre mayúsculas y minúsculas
Cómo funciona esta fórmula: La función EXACTA compara el valor de búsqueda con cada elemento de la matriz de búsqueda. Si los valores comparados son exactamente iguales, incluido el caso de los caracteres, la función devuelve VERDADERO, FALSO en caso contrario. Esta matriz de valores lógicos (donde VERDADERO representa coincidencias exactas) va al matriz_buscada argumento de XMATCH. Y debido a que el valor de búsqueda es VERDADERO, la función XMATCH devuelve la posición de la primera coincidencia exacta encontrada o la última coincidencia exacta, dependiendo de cómo configuró el modo de búsqueda
argumento.
XMATCH frente a MATCH en Excel
XMATCH fue diseñado como un reemplazo más potente y versátil de MATCH, por lo que estas dos funciones tienen mucho en común. Sin embargo, existen diferencias esenciales.
Comportamiento predeterminado diferenteLa función COINCIDIR tiene por defecto una coincidencia exacta o el siguiente elemento más pequeño ( tipo de concordancia
establecido en 1 u omitido).La función XMATCH por defecto es una coincidencia exacta ( modo_partido
establecido en 0 u omitido).
Comportamiento diferente para una coincidencia aproximada Cuando el modo_partido / tipo de concordancia
- El argumento se establece en 1:
- MATCH busca una coincidencia exacta o la siguiente más pequeña. Requiere que la matriz de búsqueda se ordene en orden ascendente.
XMATCH busca una coincidencia exacta o la siguiente más grande. No requiere ninguna clasificación. Cuando el modo_partido / tipo de concordancia
- El argumento se establece en -1:
- MATCH busca una coincidencia exacta o la siguiente más grande. Requiere ordenar la matriz de búsqueda en orden descendente.
XMATCH busca una coincidencia exacta o la siguiente más pequeña. No requiere ninguna clasificación.
Búsqueda con comodines Para encontrar coincidencias parciales con XMATCH, debe configurar el modo_partido
argumento a 2.La función COINCIDIR no tiene una opción especial de modo de coincidencia con comodines. En la mayoría de los casos, lo configurará para una coincidencia exacta ( tipo de concordancia
establecido en 0), que también funciona para búsquedas con comodines.
Modo de búsqueda Al igual que la nueva función BUSCAR XL, XMATCH tiene una función especial modo de búsqueda argumento que permite definir eldirección de búsqueda
- :
- 1 u omitido (predeterminado): busque del primero al último.
-1 – búsqueda inversa del último al primero. Y elige unalgoritmo de búsqueda binaria que es muy rápido y eficiente endatos ordenados
- .
- 2 – búsqueda binaria sobre datos ordenados de forma ascendente.
-2 – búsqueda binaria sobre datos ordenados de forma descendente.Búsqueda binaria también llamado búsqueda de medio intervalo obúsqueda logarítmica
, es un algoritmo especial que encuentra la posición de un valor de búsqueda dentro de una matriz comparándolo con el elemento central de la matriz. Una búsqueda binaria es mucho más rápida que una búsqueda normal, pero funciona correctamente sólo en listas ordenadas. En datos no clasificados, puede arrojar resultados erróneos que pueden parecer bastante normales a primera vista.
La sintaxis de MATCH no proporciona ningún argumento para el modo de búsqueda.
XMATCH maneja matrices de forma nativa A diferencia de su predecesora, la función XMATCH fue diseñada para Excel dinámico y maneja matrices de forma nativa, sin tener que presionarCtrl + Mayús + Intro
. Esto hace que las fórmulas sean mucho más fáciles de crear y editar, especialmente cuando se usan algunas funciones diferentes juntas. Simplemente compare las siguientes soluciones:
Disponibilidad de XMATCH y MATCH
XMATCH es una función nueva y solo está disponible en Excel para Microsoft 365 y Excel 2021.
La función COINCIDIR está disponible en cualquier versión de Excel 365 hasta Excel 2007.
Así es como se utiliza la función XMATCH en Excel. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded
Preguntas frecuentes sobre la función XMATCH de Excel con ejemplos de fórmulas
La función XMATCH de Excel es una herramienta poderosa que permite buscar un valor en una columna o fila y devolver su posición relativa en esa columna o fila específica. Esta función es especialmente útil cuando se trabaja con grandes conjuntos de datos y se necesita realizar búsquedas rápidas y precisas. En este artículo, responderemos a algunas de las preguntas más frecuentes sobre la función XMATCH y proporcionaremos ejemplos de fórmulas para ilustrar su uso.
1. ¿Qué es la función XMATCH y cómo funciona?
La función XMATCH es una función incorporada en Excel que permite buscar un valor específico en una columna o fila y devolver su posición relativa. Funciona mediante la comparación de los valores en una columna o fila con el valor buscado y devuelve un número que representa la posición relativa del valor en relación con la columna o fila seleccionada.
2. ¿Cuál es la sintaxis de la función XMATCH?
La sintaxis básica de la función XMATCH es la siguiente: =XMATCH(valor_buscado, rango_de_búsqueda, [tipo_de_coincidencia], [modo_de_búsqueda]).
Referencia externa: Documentación oficial de Microsoft sobre la función XMATCH
3. ¿Cuáles son los tipos de coincidencia admitidos por la función XMATCH?
La función XMATCH admite tres tipos de coincidencia:
- Exacto: buscará un valor exacto en el rango de búsqueda y devolverá la posición correspondiente.
- Menor o igual: buscará el valor más cercano que sea menor o igual al valor buscado.
- Mayor o igual: buscará el valor más cercano que sea mayor o igual al valor buscado.
4. ¿Cuál es el modo de búsqueda utilizado por defecto en la función XMATCH?
Por defecto, la función XMATCH utiliza el modo de búsqueda exacta (0), lo que significa que buscará un valor exacto en el rango de búsqueda y devolverá la posición correspondiente si se encuentra. Sin embargo, este modo puede cambiarse según las necesidades del usuario.
Ejemplo de fórmula con la función XMATCH
Supongamos que tenemos una columna de precios en la columna A y queremos buscar el precio correspondiente a un producto específico en la columna B. Podemos utilizar la siguiente fórmula:
=INDEX(A:A, XMATCH(B1, B:B, 0))
Esta fórmula buscará el valor de B1 en la columna B y devolverá el precio correspondiente de esa fila en la columna A.
Conclusión
La función XMATCH de Excel es una herramienta valiosa para realizar búsquedas precisas en grandes conjuntos de datos. Esperamos que este artículo haya respondido a tus preguntas más frecuentes sobre la función XMATCH y proporcionado ejemplos de fórmulas para ayudarte a utilizarla de manera efectiva en tu trabajo de hoja de cálculo.