Desempaquetar resultado multicolumna de BUSCARX con arrays

El problema: BUSCARX devuelve solo la primera columna

Un miembro desde Argentina tiene una fórmula que debería traerle tres datos de golpe:

=BUSCARX(A22:A28; A2:A11; E2:G11)

Busca siete valores a la vez y el rango de retorno tiene tres columnas: email, teléfono y salario. Espera una tabla de siete filas por tres columnas. Lo que obtiene es una única columna con los emails. El teléfono y el salario simplemente no aparecen.

No hay error, no hay aviso. La fórmula devuelve un tercio de lo pedido y se queda tan tranquila.

Por qué pasa: dos dimensiones que no caben en una

Hugo da la explicación. BUSCARX sabe devolver varias columnas, y lo hace perfectamente cuando el valor buscado es una sola celda: le pides una fila entera del rango de retorno y te la da completa.

También sabe buscar varios valores a la vez, devolviendo un resultado por cada uno.

Lo que no sabe hacer es las dos cosas al mismo tiempo. Al pasarle siete valores de búsqueda y un retorno de tres columnas, el resultado tendría que ser una tabla de siete por tres, y BUSCARX no está diseñada para expandirse en dos direcciones a la vez. Ante la duda, se queda con la primera columna.

Es una limitación de la función, no un fallo de la fórmula. Y como no avisa, conviene tenerla fichada: búsqueda múltiple y retorno multicolumna no se llevan bien.

Solución 1: INDICE con COINCIDIRX

La alternativa clásica separa las dos tareas. COINCIDIRX se encarga de encontrar las posiciones, e INDICE de extraer lo que hay en ellas:

=INDICE(E2:G11; COINCIDIRX(A15:A21; A2:A11; 0); SECUENCIA(;3))

La clave está en que INDICE acepta matrices en sus dos argumentos de posición. COINCIDIRX le pasa un vector vertical con las siete filas encontradas, y en el argumento de columna le pasamos un vector horizontal con los números 1, 2 y 3.

Al cruzar un vector vertical con uno horizontal, INDICE genera la tabla completa de siete por tres. Es exactamente el comportamiento que le faltaba a BUSCARX.

En el hilo original ese vector horizontal se escribe como una constante matricial. Aquí uso SECUENCIA(;3), que produce lo mismo y tiene una ventaja práctica: si mañana el rango pasa a tener cinco columnas, cambias un número en lugar de reescribir la constante. Con COLUMNAS(E2:G11) en vez del 3 quedaría incluso automático.

Solución 2: FILTRAR con BYROW

Hugo aporta un segundo camino, más de Excel moderno, que ni siquiera necesita saber cuántas columnas hay:

=FILTRAR(E2:G11; BYROW(N(A2:A11 = ENFILA(A15:A21)); SUMA))

Vale la pena desmontarlo por partes, porque el patrón se reutiliza muchísimo:

• ENFILA(A15:A21) pone los valores buscados en horizontal.
• Compararlos con la columna vertical A2:A11 produce una matriz de dos dimensiones: cada celda dice si ese registro coincide con ese valor buscado.
• N convierte esos verdaderos y falsos en unos y ceros.
• BYROW con SUMA colapsa cada fila a un único número: 1 si el registro está entre los buscados, 0 si no.
• FILTRAR se queda con las filas cuyo número no es cero, enteras, con sus tres columnas.

La diferencia importante respecto a INDICE: aquí las columnas vienen solas, porque FILTRAR nunca ha filtrado columnas, filtra filas completas. Añade una columna al rango y la fórmula sigue funcionando sin tocarla.

Cuál elegir

No son intercambiables del todo, y la diferencia decide por ti:

• INDICE con COINCIDIRX respeta el orden de la lista de búsqueda y admite repetidos: si pides el mismo valor dos veces, sale dos veces. Es lo que quieres cuando estás rellenando una plantilla con un orden dado.
• FILTRAR con BYROW devuelve las filas en el orden de la tabla origen, y cada coincidencia una sola vez. Es lo que quieres cuando lo que buscas es "dame los registros de esta lista", sin importar el orden.

Si tu lista de búsqueda es una plantilla que hay que rellenar en su orden, INDICE. Si es un conjunto de registros que quieres extraer, FILTRAR.

Funciones clave

• BUSCARX — devuelve varias columnas solo con un valor de búsqueda único. Con un array de búsqueda se queda en la primera columna.
• COINCIDIRX — devuelve posiciones, y acepta un array de valores buscados.
• INDICE — con matrices en fila y columna genera la tabla cruzada completa.
• SECUENCIA — genera el vector de números de columna sin escribir constantes a mano.
• ENFILA — reorienta un vector a horizontal para provocar la comparación cruzada.
• BYROW — colapsa la matriz resultante a un valor por fila.
• FILTRAR — devuelve filas completas, así que las columnas se conservan solas.

Conclusión

Cuando BUSCARX te devuelva menos columnas de las que esperabas, no busques el error en el rango: la fórmula está bien, es la función la que no cubre ese caso.

A partir de ahí, INDICE con COINCIDIRX si te importa el orden, FILTRAR con BYROW si te importa la flexibilidad. Ambas son sólidas y ambas escalan mejor que la original.

Estos límites poco documentados de las funciones son de lo que más se aprende en los casos de la comunidad de InflueXcel: no aparecen en la ayuda, aparecen cuando alguien se los encuentra de frente.

Más casos con estas funciones

• FILTRAR
• BYROW
• BUSCARX
• COINCIDIRX

Más contenido de Excel en InflueXcel

• Buscar prefijos de longitud variable en otra columna: BYROW, MAP, REGEX y COINCIDIRX Caso
  Interesante problema planteado por un miembro: tiene una columna A con ~1.200 referencias de longitud variable y una columna C con ~276 text
• Identificar proveedor en asientos contables con BUSCARX y BYROW Caso
  Juan comparte en el grupo una fórmula de John Vergara que le ha "salvado la vida laboral" para un problema clásico de contabilidad: en un di
• Multiplicar por coeficientes de escenario con INDICE, COINCIDIRX y SI.CONJUNTO Caso
  Un miembro de la comunidad tiene una tabla de conceptos con cantidades, precios y un campo "Escenario" (1, 2 o 3). Aparte, tiene una tabla d
• Cómo dejar una celda realmente vacía en fórmulas SI, BUSCARX y FILTRAR Caso
  Interesante problema planteado por un miembro de la comunidad: quiere que sus fórmulas devuelvan una celda realmente vacía cuando no hay res
• Buscar múltiples palabras simultáneamente con FILTRAR y BYROW Caso
  Juan consigue filtrar una tabla buscando una palabra con HALLAR, pero cuando intenta buscar dos palabras a la vez pasando un rango (K16:L16)
• Filtrar filas con todos los valores a cero: 4 enfoques con FILTRAR y BYROW Caso
  Juan tiene una tabla grande donde muchas filas contienen solo ceros y necesita filtrarlas para quedarse solo con las que tienen datos reales
• FILTRAR en Excel: las 5 dudas más buscadas, resueltas (y una extra: filtrar entre fechas) Tutorial
  Le preguntamos a Copilot cuáles son las 5 dudas que más se buscan sobre FILTRAR y las resolvemos una a una en Excel, cada una con un truco e
• BUSCARX en Excel: las 5 dudas más buscadas, resueltas (y una extra con varios criterios) Tutorial
  Le preguntamos a Copilot cuáles son las 5 dudas que más se buscan sobre BUSCARX y las resolvemos una a una en Excel, cada una con un truco e
• SUMAR.SI.CONJUNTO con referencias de celda en los criterios de fecha Caso
  Un miembro de la comunidad tiene una fórmula SUMAR.SI.CONJUNTO que funciona perfectamente con fechas escritas directamente, pero no consigue
• Payback y Payback Descontado en una sola fórmula con BYROW Caso
  Henry necesitaba calcular el Payback y el Payback Descontado de una inversión, y tenía dos fórmulas separadas con LET + SCAN que funcionaban