Devolución de filas o columnas completas

Hasta ahora, XLOOKUP ha devuelto un solo valor. Sin embargo, también puede devolver una fila o columna completa de datos de una sola vez.

Cuando una fórmula devuelve varios valores, estos se desbordan automáticamente en las celdas contiguas. Así, una sola XLOOKUP puede completar un registro breve.

El truco consiste en hacer que la matriz de resultados abarque varias columnas. En lugar de devolver solo B2:B20, devuelva B2:E20.

XLOOKUP encuentra la fila coincidente y, a continuación, devuelve todas las columnas de esa fila dentro de la matriz de resultados. Una fórmula, cuatro resultados.

Escriba la fórmula en una sola celda, por ejemplo F2, y pulse Intro. Los valores aparecerán en F2, G2, H2 e I2.

Un borde azul tenue delimita el rango desbordado. Solo puede editar la celda superior izquierda; el resto se completa mediante el desbordamiento y no se puede modificar directamente.

Una tabla de empleados tiene el ID en la columna A y el nombre, el departamento, el puesto y el salario en las columnas B a E.

Escriba un ID en D2 y una sola XLOOKUP devolverá el registro completo. Cambie el ID y toda la fila se actualizará al instante: una pequeña herramienta de búsqueda en una sola fórmula.

La misma idea funciona verticalmente. Si busca en una fila de encabezados, puede devolver una columna completa de resultados.

Aquí XLOOKUP busca una etiqueta de D2 en la fila de encabezados B1:E1 y desborda hacia abajo todo el rango B2:E50 bajo el encabezado coincidente.

Una vez que una fórmula se desborda, puede hacer referencia al rango desbordado completo utilizando la celda seguida de un signo #, como F2#.

Esto resulta muy útil: una SUMA sobre una fila desbordada sigue siendo correcta aunque cambie el ancho de la fila, porque F2# siempre significa "todo el desbordamiento de F2".

Como el resultado es una matriz, puede utilizarlo directamente en funciones que acepten rangos.

Por ejemplo, puede envolver la búsqueda en SUM para obtener el total de una fila devuelta con cifras mensuales, todo en una sola fórmula y sin celdas auxiliares.

Una fórmula que se desborda necesita celdas vacías para rellenarlas. Si alguna celda del recorrido del desbordamiento ya contiene datos, XLOOKUP devuelve un error #SPILL!.

La solución es sencilla: borre las celdas que bloquean el desbordamiento o mueva la fórmula a un área despejada. El rango desbordado debe estar completamente vacío.

Para crear una tarjeta de búsqueda pulida, también puede desbordar los encabezados de los campos.

Coloque una XLOOKUP que devuelva la fila de datos y, encima de ella, haga referencia al rango de encabezados. Cuando el desbordamiento se amplíe o se reduzca, las etiquetas seguirán alineadas con las columnas devueltas.

Incluso puede anidar una XLOOKUP dentro de otra. La función interna devuelve una columna completa y la externa selecciona una sola celda de ella.

Esto produce una verdadera búsqueda bidireccional, que coincide tanto con una fila como con una columna, utilizando únicamente XLOOKUP. Es una alternativa interesante a INDEX-MATCH-MATCH.

Ahora sabe que XLOOKUP puede devolver más de un valor:

• Una matriz de resultados con varias columnas desborda una fila completa
• Una matriz de resultados con varias filas desborda una columna completa
• Puede hacer referencia al desbordamiento con el sufijo #
• Borre las celdas que bloquean el desbordamiento para evitar #SPILL!

Así, una sola fórmula se convierte en un visor de registros completo.

Compruebe si entiende el desbordamiento de resultados de XLOOKUP.

Ha terminado el curso de XLOOKUP aprendiendo a desbordar resultados:

• Amplíe la matriz de resultados para desbordar una fila o columna completa
• Los desbordamientos rellenan las celdas contiguas vacías y muestran un borde azul
• Haga referencia a un desbordamiento con el sufijo #, como F2#
• Evite #SPILL! manteniendo despejada el área de desbordamiento

Cuando domine la sintaxis, los valores alternativos, la dirección y el desbordamiento, XLOOKUP podrá sustituir prácticamente todas las búsquedas antiguas que escriba.