Numeración automática en Excel con IF, COUNTIF y XLOOKUP

Cuando llevas registros en Excel, a menudo necesitas numerar filas de manera automática según lo que el usuario escriba en otra columna. Esta guía te muestra cómo asignar números correlativos en la columna A basándote en valores únicos que aparezcan en la columna E, sin macros.

Índice

Contexto y objetivos

Numerar registros parece trivial hasta que los datos se modifican, se insertan filas o se aplican filtros: los números fijos dejan de coincidir. Con la combinación correcta de funciones puedes mantener una numeración viva y coherente que:

Asigne el mismo número a todas las filas cuyo valor en la columna E sea idéntico.
Genere un número nuevo tan pronto como aparezca un valor diferente.
Mantenga la celda de la columna A vacía cuando la celda de la columna E esté vacía o contenga 0.

Estructura recomendada de la hoja

Para facilitar el seguimiento y evitar errores de referencias:

Coloca los encabezados en la fila 4 (es decir, la numeración real empieza en la fila 5).
Garantiza que las columnas A y E estén alineadas en cantidad de filas: de la 5 a la 34 por defecto.
Evita fórmulas volátiles (p. ej. OFFSET) cerca de estas celdas; podrían recalcular toda la hoja innecesariamente.

Fórmula paso a paso

El núcleo consiste en tres funciones (IF, COUNTIF y XLOOKUP) combinadas con una llamada a MAX. Abajo verás la versión base, pensada para filas en las que E puede quedar en blanco:


=IF($E5="", "",
     IF(COUNTIF($E$4:$E4, $E5)&gt;0,
        XLOOKUP($E5, $E$4:$E4, $A$4:$A4),
        MAX($A$4:$A4)+1))

Copiar o arrastrar esta fórmula desde A5 hasta A34 basta para cubrir las primeras 30 filas de trabajo. Aun así, entender cada bloque te ayuda a depurar y adaptar la lógica posteriormente:

Condición inicial

IF($E5="", "") devuelve cadena vacía cuando la celda E5 no tiene valor. La celda correspondiente en A permanecerá visualmente vacía, lo que simplifica filtros y suma de totales.

Detección de repetidos

COUNTIF($E$4:$E4, $E5) examina todas las filas anteriores buscando coincidencias. Si la cuenta es mayor que cero, significa que el valor ya se registró y debe recibir el mismo número identificador.

Asignación de número previo

XLOOKUP($E5, $E$4:$E4, $A$4:$A4) recupera el número ya asignado a ese valor en sus primeras apariciones. De este modo se evita duplicar la numeración.

Secuencia incremental

Cuando COUNTIF detecta que el valor es nuevo, MAX($A$4:$A4)+1 busca el número mayor existente en las filas anteriores y lo incrementa en uno. Así se garantiza una secuencia sin huecos incluso si se han eliminado filas en el pasado.

Versión que también trata el 0 como vacío

En muchas hojas los usuarios tienden a introducir ceros simplemente para “vaciar” una celda numérica. Si ese es tu caso, sustituye la primera condición por una comparación contra 0:


=IF($E5=0, "",
     IF(COUNTIF($E$4:$E4, $E5)&gt;0,
        XLOOKUP($E5, $E$4:$E4, $A$4:$A4),
        MAX($A$4:$A4)+1))

Versión robusta cuando la columna E contiene fórmulas

Las fórmulas pueden devolver "" (cadena vacía) o 0 dependiendo de cómo estén construidas. Para cubrir ambos supuestos en un único argumento:


=IF(OR($E5={"",0}), "",
     IF(COUNTIF($E$4:$E4, $E5)&gt;0,
        XLOOKUP($E5, $E$4:$E4, $A$4:$A4),
        MAX($A$4:$A4)+1))

Situación	Resultado en A
E5 = “”	“” (vacío)
E5 = 0	“” (vacío)
E5 ya apareció antes	Número existente
E5 es valor nuevo	Número secuencial máximo + 1

Análisis detallado de las funciones utilizadas

IF

Evalúa una condición y devuelve uno de dos resultados. En este caso se anidan dos IF: el primero decide si la celda debe quedar vacía y el segundo canaliza el flujo hacia COUNTIF o MAX.

COUNTIF

Devuelve el número de veces que un valor aparece dentro de un rango. Trabaja muy rápido porque solo analiza un criterio. Usar un rango que crece a medida que copias la fórmula ($E$4:$E4) evita recálculos innecesarios.

XLOOKUP

Disponible desde Office 365 y Excel 2019, elimina las limitaciones de VLOOKUP y HLOOKUP. Busca el primer valor que coincide con el criterio y devuelve el dato asociado en la misma posición relativa dentro de otro rango.

MAX

Recorre un rango para encontrar el valor numérico más alto. Al usar +1 creamos la progresión secuencial. La ventaja de MAX es que ignora celdas vacías automáticamente.

Preguntas frecuentes

¿Por qué no usar una columna auxiliar? Con la fórmula presentada evitas agregar más columnas y mantienes limpia la hoja. No obstante, si deseas documentar el estado de repetición, una columna auxiliar con COUNTIF puede ser útil para auditorías.

¿XLOOKUP es obligatorio? Si trabajas en versiones anteriores a Excel 2019, sustituye XLOOKUP por una combinación de INDEX + MATCH. La sintaxis se complica, pero el resultado es idéntico.

¿Qué ocurre si inserto filas en medio? La numeración se recalcula porque MAX y COUNTIF usan rangos absolutos hasta la fila precedente, garantizando la consistencia.

¿Puedo ocultar los ceros en E en lugar de usar OR? Sí. Configura el formato personalizado 0;-0;;@ en la columna E y los ceros no se mostrarán, aunque seguirán existiendo a nivel de celda.

Buenas prácticas de mantenimiento

Protege la columna A con la opción «Bloquear celdas» para que los usuarios no editen manualmente la numeración.
Coloca las fórmulas dentro de una tabla estructurada. Así evitarás referencias absolutas y la fórmula se extenderá automáticamente.
Documenta en un comentario la lógica de la fórmula. Ayudarás a futuros usuarios a comprender el propósito de cada función.

Optimización para grandes volúmenes

Si tu lista supera las diez mil filas, el uso repetido de COUNTIF puede ralentizar la hoja. Dos enfoques alternativos son:

Dinámicas con LET — Define la función una sola vez, almacena rangos en variables locales y reduce las llamadas repetidas.
Power Query — Importa la tabla, configura un «Índice» con agrupación por la columna E y vuelve a cargar la consulta. Obtendrás un resultado idéntico con cálculo fuera de la hoja.

Errores típicos y cómo evitarlos

Mensaje de error	Causa habitual	Solución
#VALUE!	El rango de `XLOOKUP` no tiene el mismo tamaño que el rango buscado.	Asegúrate de que `$E$4:$E4` y `$A$4:$A4` contengan la misma cantidad de filas.
Resultados duplicados	La entrada en E tiene espacios extra o diferencias de mayúsculas.	Usa `TRIM` y `UPPER` sobre la columna E antes de comparar.
Números saltados	Filas eliminadas manualmente.	Recalcula copiando la fórmula hacia abajo o ajusta `MAX` para que ignore huecos mediante `AGGREGATE`.

Alternativas con funciones dinámicas

Si dispones de Excel 365, obtener la misma lógica en una sola celda es posible gracias a UNIQUE, XMATCH y SEQUENCE:


=LET(
 valores, FILTER($E$5:$E$34, ($E$5:$E$34&lt;&gt;"")*($E$5:$E$34&lt;&gt;0)),
 unicos, UNIQUE(valores),
 indices, XMATCH(valores, unicos),
 indices)

Esta fórmula se ubica en A5 y se derrama automáticamente hacia abajo. No obstante, la compatibilidad con versiones anteriores es limitada, de modo que la solución desarrollada al inicio sigue siendo la opción más transversal.

Conclusiones

La numeración automática basada en valores únicos te permite crear reportes, paneles y sistemas de inventario mucho más robustos. Con solo cuatro funciones clásicas —IF, COUNTIF, XLOOKUP y MAX— obtienes un mecanismo flexible, fácil de mantener y ampliamente compatible. Si luego tu archivo evoluciona, siempre podrás migrar a opciones más potentes como funciones dinámicas o Power Query, pero contarás con un punto de partida sólido y entendible.