Usa IFNA alrededor de una búsqueda desbordada para completar sus resultados #N/A con datos de una segunda tabla de referencia. Este ejemplo de Excel para Mac busca etiquetas de recursos gráficos, conserva las cantidades encontradas en la tabla principal y señala claramente las etiquetas que faltan en ambas tablas.

¿Qué versiones de Excel para Mac admiten este método?

Usa Excel para Microsoft 365 para Mac, Excel 2024 para Mac o Excel 2021 para Mac. Microsoft incluye estas ediciones tanto para XLOOKUP como para IFNA. La documentación de XLOOKUP excluye explícitamente Excel 2016 y Excel 2019, aunque esas ediciones aparezcan en la lista de aplicabilidad general de la página. Las fórmulas siguientes están destinadas a las ediciones para Mac compatibles. Función XLOOKUP, función IFNA

La tarea es específica: tienes una columna de etiquetas de activos solicitados y quieres una cantidad por etiqueta. La referencia principal tiene prioridad; la segunda referencia proporciona cantidades solo cuando la búsqueda principal devuelve #N/A. Todas las etiquetas y cantidades siguientes son datos sintéticos.

Configurar dos tablas de referencia y una lista de activos solicitados

Empieza en una hoja de cálculo en blanco de un libro. Usa las ubicaciones de celdas indicadas a continuación para que las fórmulas funcionen sin cambiar sus referencias. Estas pequeñas tablas de referencia son rangos normales de la hoja; no hace falta convertirlas en objetos de tabla de Excel.

Introduce la referencia principal en D1:E4:

Celda de la columna D Etiqueta del activo Celda de la columna E Cantidad
D1 Activo E1 Cantidad
D2 Contorno de hoja E2 8
D3 Textura de puntos E3 0
D4 Patrón de mosaico E4 12

Introduce la segunda referencia en G1:H5:

Celda de la columna G Etiqueta del activo Celda de la columna H Cantidad
G1 Activo H1 Cantidad
G2 Textura de puntos H2 9
G3 Boceto de curva H3 5
G4 Textura granulada H4 3
G5 Contorno de hoja H5 10

Escribe Activo solicitado en J1 y luego introduce estas cinco etiquetas en J2:J6, una por celda y en este orden:

  1. Contorno de hoja
  2. Textura de puntos
  3. Boceto de curva
  4. Textura granulada
  5. Motivo de arco

Mantén las cantidades como valores numéricos y rellena todas las celdas de cantidad de las dos referencias. En este ejemplo, cero significa que la cantidad registrada es cero. No significa que falte el activo.

Crear la lista desbordada de etiquetas

Introduce los encabezados Activo, Cantidad principal y Cantidad completada en A1, B1 y C1. Deja vacío A2:C20 y, a continuación, introduce esta fórmula en A2:

=FILTER(J2:J20,J2:J20<>"","")

Esta fórmula filtra las celdas vacías de la lista de solicitudes. Con los datos proporcionados, desborda las cinco etiquetas de activos en A2:A6. Microsoft documenta FILTER para las tres ediciones para Mac indicadas arriba y explica que su argumento final proporciona un resultado cuando ninguna fila cumple la condición. En este caso, ese resultado es una cadena vacía. Función FILTER

Las fórmulas posteriores usan A2# para hacer referencia a toda esta matriz desbordada. El operador # se ajusta al tamaño de la matriz, por lo que evita fijar el extremo actual como A6. Microsoft describe este comportamiento en la documentación del operador de rango desbordado.

Completar las cantidades faltantes con la segunda referencia

Primero, crea la búsqueda principal en B2:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

Busca en la columna de activos principal y devuelve las cantidades correspondientes. XLOOKUP usa la coincidencia exacta de forma predeterminada y devuelve #N/A cuando no encuentra ninguna coincidencia y no se proporciona un resultado alternativo. Función XLOOKUP

En el ejemplo, B2:B6 debería contener 8, 0, #N/A, #N/A y #N/A. Conserva esos errores de coincidencia para el paso siguiente.

Ahora introduce la fórmula para completar los resultados en C2:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA examina la matriz desbordada principal. En las posiciones que contienen #N/A, la fórmula proporciona el resultado correspondiente de la segunda búsqueda. Los demás resultados principales se mantienen sin cambios. Microsoft documenta específicamente que IFNA devuelve una matriz de resultados cuando su primer argumento es una fórmula matricial. Función IFNA

El resultado esperado en la hoja es:

Activo en la columna A Cantidad principal en la columna B Cantidad completada en la columna C
Contorno de hoja 8 8
Textura de puntos 0 0
Boceto de curva #N/A 5
Textura granulada #N/A 3
Motivo de arco #N/A No aparece en la lista

Lee estas filas como una comprobación de prioridad. Contorno de hoja conserva 8 aunque la segunda referencia contenga 10. Textura de puntos conserva 0 aunque la segunda referencia contenga 9. Boceto de curva y Textura granulada reciben sus cantidades secundarias. Motivo de arco sigue sin resolverse de forma visible porque no aparece en ninguna referencia.

Las dos matrices deben describir los mismos activos solicitados en el mismo orden. En esta configuración, ambas se originan en A2#. Si adaptas un libro existente, no combines un resultado principal ordenado de una forma con una búsqueda alternativa basada en una lista de etiquetas con otro orden.

Usar una sola fórmula si no necesitas la columna intermedia

Para mantener juntas la búsqueda principal y la operación para completar resultados, sustituye la fórmula de C2 por esta alternativa completa:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

Usa los mismos datos de ejemplo y debería producir las mismas cinco cantidades completadas. La columna B ya no es necesaria para el cálculo. Mantener la versión de dos pasos durante la configuración del libro facilita ver qué referencia proporciona cada resultado.

Determinar si realmente falta un resultado

Antes de adaptar la fórmula, inspecciona lo que devuelve la primera búsqueda. Una etiqueta que falta, un resultado que parece vacío y una cantidad igual a cero requieren decisiones distintas.

Resultado principal Qué hace esta fórmula para completar resultados Qué comprobar
Cantidad numérica, incluido 0 La conserva Confirma que sea la cantidad principal prevista.
#N/A Usa la búsqueda secundaria Confirma que el error indique que falta la etiqueta.
Cadena vacía, "" La conserva Comprueba si la fórmula principal oculta las coincidencias faltantes.
Otro error Lo deja visible Repara la fórmula o referencia subyacente.

Por ejemplo, si la búsqueda principal existente usa "" como argumento if_not_found, IFNA no puede reconocer esa cadena vacía como #N/A. Elimina ese argumento de la búsqueda principal, como se muestra en B2, o usa la fórmula combinada de arriba. La lógica para completar resultados depende de conservar un valor reconocible que indique que no hubo coincidencia. Función XLOOKUP, función IFNA

Inspecciona también las propias cantidades de las referencias. IFNA responde ante un valor de error; no comprueba si se ha rellenado una celda de cantidad. Si un registro principal coincidente tiene una cantidad pendiente, completa ese registro o define una regla distinta para las cantidades pendientes antes de aplicar este flujo de trabajo.

Hay otra distinción: un error #N/A existente en los datos de cantidad principales también activará esta alternativa. En este ejemplo, el requisito previo es que las columnas de cantidades contengan valores numéricos válidos y que #N/A solo aparezca cuando una búsqueda no encuentra coincidencia. Comprueba esa condición antes de considerar cada sustitución como una etiqueta de activo sin coincidencia.

Comprobar la prioridad y mantener activa la matriz desbordada

Usa las cinco filas del ejemplo como una pequeña comprobación de aceptación antes de sustituirlas por tus propias etiquetas de activos. En conjunto, prueban una coincidencia principal, un cero válido, dos coincidencias secundarias y una etiqueta ausente de ambas referencias.

Como comprobación adicional, cambia temporalmente la cantidad secundaria de Contorno de hoja de 10 a 14. La cantidad completada debería seguir siendo 8. Luego cambia la cantidad secundaria de Boceto de curva de 5 a 6; su cantidad completada debería pasar a ser 6. Después, restablece los valores originales. Estas son comprobaciones ilustrativas para tu libro, no resultados de pruebas de Excel comunicados.

Mantén un solo registro previsto por etiqueta de activo en cada referencia. XLOOKUP normalmente busca desde el primer elemento y devuelve la primera coincidencia, así que no des por sentado que se combinarán las cantidades de etiquetas repetidas. Resuelve cómo tratar las etiquetas duplicadas antes de confiar en este diseño de búsqueda. Función XLOOKUP

Despejar un rango de salida bloqueado

Introduce cada fórmula desbordada únicamente en su celda inicial. Excel rellena automáticamente las celdas contiguas, y solo se puede editar la celda superior izquierda de una matriz desbordada. Coloca las fórmulas fuera de los objetos de tabla de Excel; Microsoft admite el uso de tablas como datos de origen, pero no admite fórmulas desbordadas dentro de ellas. Fórmulas de matriz dinámica y comportamiento de las matrices desbordadas

Si ves #SPILL!, selecciona la celda de la fórmula para ver el límite previsto de los resultados. Si está disponible, usa la opción Select Obstructing Cells de la alerta de error y mueve las entradas existentes que bloquean el resultado. Las celdas combinadas también pueden impedir el desbordamiento; mueve la salida o separa las celdas afectadas. Cómo corregir un error #SPILL!

Ampliar las entradas de forma deliberada

La fórmula de la lista solicitada examina J2:J20. Las etiquetas añadidas dentro de ese rango se incluyen automáticamente; para las etiquetas introducidas por debajo, hay que ampliar el rango de la fórmula. Del mismo modo, las búsquedas de referencia usan rangos fijos que terminan en las filas 4 y 5. Al añadir registros, amplía conjuntamente cada rango de activos y su rango de cantidades correspondiente.

En una hoja más grande, las tablas de Excel con referencias estructuradas pueden ajustar los rangos de origen cuando se añaden o eliminan filas. Mantén las fórmulas de salida en la cuadrícula de la hoja, fuera de esas tablas. Fórmulas de matriz dinámica y comportamiento de las matrices desbordadas

Mantén las entradas de este ejemplo en el mismo libro. Microsoft documenta una compatibilidad limitada con matrices dinámicas entre libros: las matrices dinámicas vinculadas requieren que ambos libros permanezcan abiertos, y actualizar los datos después de cerrar el origen puede producir #REF!. Fórmulas de matriz dinámica y comportamiento de las matrices desbordadas

Cuando las filas coincidan con el resultado esperado, usa la columna completada como resultado de cantidades de activos. Conserva la regla de prioridad de la tabla principal, mantén los ceros significativos y deja las etiquetas ausentes de ambas referencias marcadas como No aparece en la lista para que puedas completar los datos de referencia cuando corresponda.

Fuentes