XLOOKUP de Excel devuelve una coincidencia incorrecta con claves duplicadas: solución

Usas XLOOKUP en Excel esperando la coincidencia correcta, pero devuelve el valor incorrecto cuando tu columna de búsqueda contiene claves duplicadas. Esto sucede porque XLOOKUP por defecto devuelve la primera coincidencia que encuentra, y cuando existen duplicados, la primera coincidencia puede no ser la que pretendías. En este artículo, aprenderás por qué XLOOKUP se comporta así con duplicados y cómo forzarlo a devolver la coincidencia correcta usando técnicas específicas.

Por qué XLOOKUP devuelve la coincidencia incorrecta con claves duplicadas

XLOOKUP busca un valor especificado en una matriz de búsqueda y devuelve un valor correspondiente de una matriz de retorno. Cuando la matriz de búsqueda contiene claves duplicadas, XLOOKUP se detiene en la primera ocurrencia que encuentra. Por defecto, XLOOKUP usa un modo de búsqueda de 1, que busca desde el primer elemento hasta el último. Si tus datos tienen claves duplicadas y la coincidencia prevista no es la primera ocurrencia, XLOOKUP devuelve la coincidencia incorrecta.

Este comportamiento es intencional y coincide con cómo VLOOKUP e INDEX/MATCH también manejan los duplicados. El problema no es un error en XLOOKUP, sino un malentendido de cómo funciona la función con claves no únicas. Para solucionarlo, debes cambiar la dirección de búsqueda, hacer que tus claves sean únicas o usar una fórmula más avanzada que filtre los duplicados antes de la coincidencia.

Cómo afectan los modos de búsqueda de XLOOKUP a la coincidencia con duplicados

XLOOKUP tiene un quinto argumento opcional llamado search_mode. El valor predeterminado es 1 (búsqueda de primero a último). Otras opciones incluyen -1 (búsqueda de último a primero), 2 (búsqueda binaria ascendente) y -2 (búsqueda binaria descendente). Los modos de búsqueda binaria requieren datos ordenados y no son adecuados para duplicados no ordenados. El modo de búsqueda de último a primero (-1) es útil cuando necesitas la entrada duplicada más reciente, por ejemplo, la transacción más reciente o la última actualización.

Cuando los duplicados son inevitables

En muchos conjuntos de datos del mundo real, las claves duplicadas son legítimas. Por ejemplo, un informe de ventas puede tener múltiples filas para el mismo ID de producto en diferentes fechas. Si deseas coincidir con la venta más reciente, necesitas ordenar los datos por fecha descendente y usar el modo de búsqueda 1, o mantener los datos como están y usar el modo de búsqueda -1 después de ordenar por fecha ascendente. La solución depende de qué duplicado necesitas: el primero, el último o uno específico basado en otra condición.

Pasos para solucionar XLOOKUP cuando devuelve la coincidencia incorrecta

Los siguientes métodos te ayudarán a forzar a XLOOKUP para que devuelva la coincidencia correcta cuando tu columna de búsqueda contenga duplicados. Elige el método que se ajuste a tu estructura de datos y al duplicado específico que necesitas.

Método 1: Cambiar el modo de búsqueda para devolver la última coincidencia

Si tus datos están ordenados de modo que la coincidencia prevista sea la última ocurrencia (por ejemplo, la fecha más reciente), usa el modo de búsqueda -1.

1. Identifica tu rango de datos
   Confirma que tu matriz de búsqueda contiene claves duplicadas y que los datos están ordenados con la coincidencia prevista como la última ocurrencia. Por ejemplo, ordena por fecha ascendente para que la fecha más reciente esté al final.
2. Escribe la fórmula XLOOKUP con el modo de búsqueda -1
   Usa esta sintaxis: =XLOOKUP(lookup_value, lookup_array, return_array, , , -1). El quinto argumento (si_no_se_encuentra) se omite dejándolo en blanco con dos comas. El sexto argumento establece el modo de búsqueda en -1.
3. Presiona Enter y verifica el resultado
   Excel devuelve el valor de la última fila coincidente en la matriz de retorno. Prueba con un duplicado conocido para confirmar que elige la fila correcta.

Método 2: Crear una columna auxiliar para hacer que las claves sean únicas

Cuando necesitas un duplicado específico que no es ni el primero ni el último, crea una clave única agregando un número de índice a cada valor duplicado.

1. Agrega una columna auxiliar junto a tu matriz de búsqueda
   Inserta una nueva columna a la derecha de tu columna de búsqueda. En la primera fila de datos, ingresa esta fórmula: =A2&COUNTIF($A$2:A2, A2). Reemplaza A2 con la referencia de celda real de tu búsqueda. Esta fórmula combina el valor original con su recuento de ocurrencias hasta el momento.
2. Copia la fórmula hacia abajo en la columna auxiliar
   Arrastra el controlador de relleno para aplicar la fórmula a todas las filas. Cada duplicado ahora tiene un identificador único: por ejemplo, “ProductoA1”, “ProductoA2”, “ProductoA3”.
3. Construye la fórmula XLOOKUP usando la columna auxiliar
   Usa esta sintaxis: =XLOOKUP(lookup_value & occurrence_number, helper_column, return_array). Reemplaza occurrence_number con el duplicado específico que deseas. Por ejemplo, para obtener la tercera ocurrencia: =XLOOKUP("ProductA" & 3, C2:C100, B2:B100).
4. Presiona Enter y verifica
   XLOOKUP ahora busca en claves únicas y devuelve la coincidencia correcta para la ocurrencia especificada.

Método 3: Usar FILTER para prefiltrar los datos

Cuando el duplicado correcto está determinado por una condición en otra columna, usa FILTER para reducir la matriz de búsqueda antes de aplicar XLOOKUP.

1. Identifica la condición que aísla el duplicado correcto
   Por ejemplo, deseas el registro de venta para ProductoA donde la columna Estado sea igual a “Completado”.
2. Escribe una fórmula FILTER dentro de XLOOKUP
   Usa esta sintaxis: =XLOOKUP(lookup_value, FILTER(lookup_array, condition_array=condition_value), FILTER(return_array, condition_array=condition_value)). Por ejemplo: =XLOOKUP("ProductA", FILTER(A2:A100, C2:C100="Completed"), FILTER(B2:B100, C2:C100="Completed")).
3. Presiona Enter y verifica
   XLOOKUP ahora solo ve filas que cumplen la condición, eliminando duplicados no deseados.

Si XLOOKUP aún devuelve la coincidencia incorrecta después de la solución principal

XLOOKUP devuelve #N/A incluso cuando el valor existe

Esto sucede a menudo cuando el enfoque de columna auxiliar crea claves que no coinciden exactamente con el valor de búsqueda. Verifica que la fórmula de la columna auxiliar use las referencias de rango correctas y que el número de ocurrencia en el valor de búsqueda coincida con el formato de la columna auxiliar. También verifica que no haya espacios adicionales. Usa la función TRIM tanto en el valor de búsqueda como en la columna auxiliar para eliminar caracteres invisibles.

XLOOKUP devuelve la coincidencia incorrecta después de ordenar

Cuando cambias el orden de clasificación de los datos, XLOOKUP con modo de búsqueda 1 o -1 puede comportarse de manera diferente porque la primera o última ocurrencia cambia. Después de ordenar, verifica siempre que el modo de búsqueda coincida con tu posición prevista. Si necesitas una ocurrencia específica independientemente del orden de clasificación, usa el método de columna auxiliar en su lugar.

XLOOKUP funciona en un libro pero no en otro

Esto suele suceder cuando el segundo libro contiene patrones de duplicados diferentes o los datos no están ordenados como se espera. Copia la fórmula exacta del libro que funciona y ajusta los rangos. Asegúrate de que ambos libros usen la misma versión de Excel que admita XLOOKUP (Excel 2021 o Microsoft 365).

Modos de búsqueda de XLOOKUP para claves duplicadas: Comparación

Modo de búsqueda	Comportamiento con duplicados	Mejor caso de uso
1 (predeterminado, de primero a último)	Devuelve la primera fila coincidente de arriba a abajo	Cuando el primer duplicado es la coincidencia prevista
-1 (de último a primero)	Devuelve la última fila coincidente de abajo a arriba	Cuando el último duplicado es la coincidencia prevista (por ejemplo, la fecha más reciente)
2 (binario ascendente)	Requiere datos ordenados; devuelve cualquier coincidencia (indefinido con duplicados no ordenados)	Conjuntos de datos grandes ordenados con claves únicas solamente
-2 (binario descendente)	Requiere datos ordenados; devuelve cualquier coincidencia (indefinido con duplicados no ordenados)	Conjuntos de datos grandes ordenados con claves únicas solamente

Ahora puedes forzar a XLOOKUP para que devuelva la coincidencia correcta incluso cuando tu columna de búsqueda contenga claves duplicadas. Comienza identificando si necesitas el primero, el último o un duplicado específico, y luego aplica el método correspondiente. Para conjuntos de datos donde los duplicados son inevitables, considera usar la columna auxiliar con COUNTIF para crear claves únicas. Si trabajas frecuentemente con claves duplicadas, explora la función FILTER para prefiltrar tus datos antes de la coincidencia, lo que te da control total sobre qué fila evalúa XLOOKUP.