Las fórmulas anidadas en Excel son la herramienta más potente de la hoja de cálculo y, a la vez, la que más tiempo hace perder: una función dentro de otra, dentro de otra, hasta que nadie recuerda qué hacía cada parte. La IA es especialmente útil en este punto, porque puede explicar una fórmula larga por capas, proponer una versión más sencilla y ayudarte a construir la tuya a partir de una descripción. Esta guía se centra en las búsquedas con BUSCARX, ÍNDICE y COINCIDIR, con cinco patrones reutilizables y los prompts para sacarles partido.
Qué es una fórmula anidada y por qué se vuelve ilegible
Una fórmula anidada usa el resultado de una función como argumento de otra. Por ejemplo, SI.ERROR(INDICE(...;COINCIDIR(...));"No existe") combina tres funciones: la más interna busca una posición, la intermedia devuelve el valor de esa posición y la externa controla el error. Cada capa tiene sentido por separado, pero leídas de golpe, y con referencias a rangos, cuesta seguirlas. El truco es no leerlas de golpe: se entienden de dentro hacia fuera.
Método para entender cualquier fórmula anidada con IA
- Pega la fórmula exacta y una muestra pequeña de datos ficticios con la misma estructura que los tuyos.
- Pide una explicación por capas, de la función más interna a la más externa, indicando qué devuelve cada capa con esos datos.
- Comprueba la explicación en Excel con la herramienta de depuración integrada: selecciona la celda, ve a Fórmulas → Auditoría de fórmulas → Evaluar fórmula y pulsa Evaluar para avanzar paso a paso. Microsoft explica el proceso en su guía para evaluar una fórmula anidada un paso cada vez.
También puedes seleccionar una parte de la fórmula en la barra de fórmulas y pulsar F9 para ver su resultado. Después pulsa Esc: si no lo haces, esa parte quedará sustituida por su valor.
«Explícame esta fórmula de Excel por capas, de la función más interna a la más externa. Para cada capa, di qué devuelve con estos datos de ejemplo: [tabla pequeña con datos ficticios]. Fórmula: [fórmula]. Uso Excel en español, versión [versión].»
Datos de ejemplo
Los patrones de abajo usan datos ficticios. La tabla Productos está en A1:E6:
| Código | Producto | Categoría | Precio | Stock |
|---|---|---|---|---|
| P001 | Teclado | Periféricos | 24,90 | 120 |
| P002 | Ratón | Periféricos | 12,50 | 80 |
| P003 | Monitor 24″ | Pantallas | 139,00 | 15 |
| P004 | Portátil 15″ | Equipos | 599,00 | 8 |
| P005 | Auriculares | Periféricos | 34,90 | 0 |
El código que se busca está en la celda G2.
Patrón 1: buscar un valor y controlar el error
Con BUSCARX, el control de errores viene incluido en el cuarto argumento:
=BUSCARX(G2;A2:A6;D2:D6;"No existe")Con ÍNDICE y COINCIDIR (compatible con versiones anteriores) hace falta anidar una tercera función:
=SI.ND(INDICE(D2:D6;COINCIDIR(G2;A2:A6;0));"No existe")Cómo se lee, de dentro hacia fuera: COINCIDIR(G2;A2:A6;0) devuelve la posición del código dentro de la lista (el 0 exige coincidencia exacta); INDICE(D2:D6;posición) devuelve el precio de esa posición; y SI.ND sustituye el error #N/A por el texto indicado. Con el código P003, el resultado es 139.
Un detalle importante: SI.ND solo captura el error #N/A, es decir, «no encontrado». SI.ERROR captura todos los errores y puede esconder fallos reales, como un rango mal escrito. Si solo esperas que a veces no exista el código, es más seguro SI.ND.
Patrón 2: buscar en una tabla y, si no está, en otra
BUSCARX admite otro BUSCARX en su argumento de «no encontrado», lo que permite encadenar búsquedas de respaldo. Imagina una hoja Activos y otra Antiguos, con el código en la columna A y el nombre en la columna C:
=BUSCARX(A2;Activos!A:A;Activos!C:C;BUSCARX(A2;Antiguos!A:A;Antiguos!C:C;"No encontrado"))La fórmula busca primero en Activos; si no encuentra el código, lo busca en Antiguos; y si tampoco está, muestra «No encontrado». Si manejas muchas filas, limita los rangos a las filas reales en lugar de columnas completas para que el libro vaya más ágil.
Patrón 3: buscar con dos criterios
Hasta ahora buscábamos por una sola columna. Para localizar el importe de un vendedor en un mes concreto, se combinan dos condiciones. La tabla Ventas (A2:C7) tiene Vendedor, Mes e Importe; el vendedor está en F2 y el mes en G2:
=BUSCARX(1;(A2:A7=F2)*(B2:B7=G2);C2:C7;"Sin datos")Con ÍNDICE y COINCIDIR:
=INDICE(C2:C7;COINCIDIR(1;(A2:A7=F2)*(B2:B7=G2);0))La clave está en el término (A2:A7=F2)*(B2:B7=G2): cada comparación genera una lista de VERDADERO/FALSO, y al multiplicarlas se obtiene 1 solo en las filas donde se cumplen las dos condiciones. Después se busca ese 1. En Excel 2021 y Microsoft 365 funciona directamente; en versiones anteriores, la versión con ÍNDICE y COINCIDIR se confirma con Ctrl + Mayús + Intro.
Patrón 4: buscar por tramos
Para aplicar un porcentaje según el tramo en el que cae un importe, el modo de coincidencia de BUSCARX evita anidar varios SI. Si los umbrales están en F2:F5 (0; 5.000; 10.000; 20.000) y los porcentajes en G2:G5 (0 %; 2 %; 3 %; 5 %), con la venta en B2:
=BUSCARX(B2;$F$2:$F$5;$G$2:$G$5;0;-1)El valor -1 en el modo de coincidencia significa «coincidencia exacta o, si no existe, el siguiente elemento más pequeño», tal como describe la documentación oficial de BUSCARX. Con una venta de 12.000 €, la fórmula devuelve el 3 %. Si olvidas ese argumento, BUSCARX buscará coincidencia exacta y devolverá un error con casi cualquier importe.
Patrón 5: buscar en fila y columna a la vez
Una tabla de tarifas de envío tiene los tramos de peso en A2:A5 («Hasta 1 kg», «Hasta 5 kg», «Hasta 10 kg», «Hasta 20 kg») y las zonas en B1:D1 («Zona 1», «Zona 2», «Zona 3»), con los precios en B2:D5. Para buscar el precio de un tramo (en H1) y una zona (en H2):
=INDICE(B2:D5;COINCIDIR(H1;A2:A5;0);COINCIDIR(H2;B1:D1;0))La alternativa con BUSCARX anida una búsqueda dentro de otra, un patrón que Microsoft describe como similar a usar ÍNDICE y COINCIDIR juntas:
=BUSCARX(H1;A2:A5;BUSCARX(H2;B1:D1;B2:D5))La búsqueda interna localiza la columna de la zona y devuelve toda esa columna de precios; la externa busca el tramo de peso dentro de esa columna y devuelve el precio.
Cómo hacer legible una fórmula larga con LET
Si tu versión de Excel lo admite (Excel 2021 o posterior y Microsoft 365), la función LET permite dar nombres a los pasos intermedios. Comparemos una fórmula que calcula el precio con un 21 % de IVA y controla el error:
=SI.ND(INDICE(D2:D6;COINCIDIR(G2;A2:A6;0))*1,21;"No existe")=LET(fila;COINCIDIR(G2;A2:A6;0);precio;INDICE(D2:D6;fila);SI.ND(precio*1,21;"No existe"))Las dos devuelven lo mismo, pero la segunda se lee como una receta: primero la fila, después el precio y, por último, el cálculo con control de error. Puedes pedírselo a la IA con un prompt como este:
«Reescribe esta fórmula con LET, dando nombres claros a cada paso, sin cambiar el resultado. Después dame dos casos de prueba (uno que exista y otro que no) con el valor esperado en cada uno.»
Prompts para construir fórmulas anidadas
- Construir: «Tengo una tabla en A1:E6 con Código, Producto, Categoría, Precio y Stock. Necesito [objetivo] a partir del código de la celda G2. Dame primero la fórmula más simple posible y después una alternativa compatible con Excel 2019. Uso Excel en español con punto y coma como separador.»
- Explicar: usa el prompt del método de arriba, siempre con datos de ejemplo ficticios.
- Auditar: «Revisa esta fórmula y dime en qué casos devolvería un resultado incorrecto sin mostrar ningún error.»
Errores frecuentes en búsquedas anidadas
- Rangos de distinto tamaño: el rango donde buscas y el que devuelves deben tener la misma longitud. Si no, aparecen errores o resultados desplazados.
- COINCIDIR sin el 0 final: sin ese argumento hace una búsqueda aproximada que exige datos ordenados, y puede devolver un resultado equivocado sin avisar.
- Números guardados como texto o espacios ocultos: la búsqueda no encuentra un valor que parece idéntico. Consulta la guía para limpiar y ordenar datos en Excel con IA.
- Usar SI.ERROR para todo: oculta errores reales. Prefiere SI.ND cuando solo esperes «no encontrado».
- Usar BUSCARX en Excel 2016 o 2019: Microsoft indica que no está disponible en esas versiones. Si compartes el libro con alguien que las usa, utiliza ÍNDICE y COINCIDIR.
- No probar con un caso conocido: comprueba la fórmula con un código cuyo resultado sepas de antemano antes de arrastrarla.
Cómo comprobar el resultado
Una fórmula anidada que «da un número» no está necesariamente bien. Prueba tres casos: uno que exista, uno que no exista y uno con datos raros (celdas vacías, espacios o duplicados). Si una fórmula te devuelve un error que no entiendes, sigue el método de la guía para depurar errores en fórmulas de Excel con IA. Y antes de usar los resultados en un informe, aplica el procedimiento de verificación de cálculos de la IA en Excel.
Siguientes pasos
Una vez dominadas las búsquedas anidadas, el siguiente paso natural es aplicarlas a procesos completos. Mira los casos prácticos de automatización de tareas repetitivas con IA.
Artículos relacionados
Más guías sobre este tema en IA en Excel: fórmulas y automatización. También te pueden interesar: