¿Cómo encontrar el valor más cercano o próximo en Excel?

Supongamos que tiene una lista de números en la columna B y necesita identificar cuál está más cerca de un valor determinado, como por ejemplo 18. Con una fórmula matricial en Excel, podrá encontrarlo de forma rápida y eficiente, sin tener que revisar manualmente toda la lista.

Para empezar, seleccione una celda vacía e introduzca la siguiente fórmula. Una vez escrita, asegúrese de pulsar Ctrl + Mayús + Entrar en lugar de solo Entrar. Esto garantizará que la fórmula se ejecute como una fórmula matricial, lo cual es necesario para que funcione correctamente:

=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))

Este enfoque es el más adecuado cuando necesita encontrar el número más cercano dentro de un rango continuo. Funciona perfectamente en la mayoría de los casos donde la precisión numérica y las coincidencias exactas son fundamentales. No obstante, tenga en cuenta que las fórmulas matriciales pueden consumir muchos recursos con conjuntos de datos muy grandes. Si experimenta problemas de rendimiento o recibe mensajes de error como #¡VALOR!, revise cuidadosamente sus referencias de celda y asegúrese de pulsar correctamente Ctrl + Mayús + Entrar.

A veces no solo necesita el valor más cercano, sino también seleccionar todos los números que se encuentren dentro de un rango determinado respecto a su valor objetivo, lo que habitualmente se conoce como rango de desviación. Kutools para Excel ofrece una solución práctica mediante su función Seleccionar celdas especiales, que le permite seleccionar rápidamente todos los valores comprendidos dentro de una diferencia especificada respecto a su objetivo.

Por ejemplo, supongamos que su valor objetivo es 18 y ha establecido una desviación de 2. Esto significa que desea seleccionar todos los valores de su rango comprendidos entre 16 (18 – 2) y 20 (18 + 2). A continuación, le explicamos cómo lograrlo paso a paso:

1. Seleccione el rango que desea buscar (por ejemplo, B3:B22) y vaya a Kutools > Seleccionar > Seleccionar celdas específicas.

2. En el cuadro de diálogo Seleccionar celdas específicas:

• En Seleccionar tipo, elija Celda.
• En Especificar tipo:
  - Establezca el primer menú desplegable en Mayor o igual que e introduzca 16 en el cuadro.
  - Establezca el segundo menú desplegable en Menor o igual que e introduzca 20.

3. Haga clic en Aceptar para ejecutarlo. Kutools le notificará cuántas celdas cumplen sus criterios y resaltará todos los valores más cercanos dentro de la desviación especificada, tal como se muestra a continuación:

Esta solución es ideal para identificar rápidamente todos los valores próximos de forma masiva, especialmente al trabajar con rangos amplios y tolerancias variables. Tenga en cuenta que la precisión de su selección depende de definir claramente su desviación: si esta es demasiado estrecha o amplia, podría omitir Datos Relevantes o incluir valores no deseados.

Para usuarios que buscan automatizar tareas o necesitan realizar búsquedas personalizadas del valor más cercano —tanto numérico como textual— en múltiples hojas o en grandes volúmenes de datos, una macro VBA ofrece una solución eficiente y flexible. Al programar Excel para comparar sistemáticamente la diferencia entre su objetivo y todos los candidatos, podrá obtener no solo el número más cercano, sino también la cadena de texto más próxima según la distancia textual.

Este enfoque resulta especialmente ventajoso cuando se necesita automatización integrada, ya sea en rangos demasiado extensos para métodos manuales o al aplicar tareas recurrentes. No obstante, tenga en cuenta que las macros de VBA requieren que las macros estén habilitadas y un conocimiento básico del entorno VBA. Antes de ejecutar cualquier macro, realice siempre una copia de seguridad de sus datos para evitar pérdidas no deseadas.

1. Haga clic en Desarrollador > Visual Basic. En la ventana Microsoft Visual Basic para Aplicaciones, haga clic en Insertar > Módulo y copie el siguiente código en el módulo:

Function FindClosest(rng As Range, target As Double) As Double
    Dim cell As Range
    Dim minDiff As Double
    Dim closestValue As Double
    
    minDiff = 1E+99
    For Each cell In rng
        If Abs(cell.Value - target) < minDiff Then
            minDiff = Abs(cell.Value - target)
            closestValue = cell.Value
        End If
    Next cell  
    FindClosest = closestValue
End Function 

2. A continuación, vaya a su hoja de cálculo e introduzca esta fórmula: =FindClosest(B3:B22; E2) en una celda vacía. Pulse Entrar para obtener el valor más cercano.

Al revisar o presentar datos, suele resultar muy útil identificar visualmente los valores más cercanos a un objetivo sin necesidad de filtrar ni reorganizar sus datos. La función integrada de Excel Usar formato condicional le permite resaltar las celdas más próximas a su valor objetivo, facilitando su localización de un simple vistazo. Aunque este método no devuelve el valor exacto en sí, resulta altamente eficaz para un análisis de datos rápido y un impacto visual inmediato.

La principal ventaja de este método es su resaltado dinámico y no destructivo, que se adapta automáticamente cuando cambian los datos o el valor objetivo. Es ideal para paneles, presentaciones y revisiones en las que la visibilidad es clave. Sin embargo, puede perder precisión si varios valores presentan la misma «proximidad», y además no genera el valor en sí para su procesamiento posterior.

1. Seleccione el rango de celdas que quiera analizar (por ejemplo, B3:B22).

2. En la pestaña Inicio, haga clic en Usar formato condicional > Nueva regla.

3. Elija Usar una fórmula para determinar qué celdas dar formato en el cuadro de diálogo. Luego, en el cuadro de fórmulas, introduzca la siguiente fórmula:

=ABS(B3-$E$2)=MIN(ABS($B$3:$B$22-$E$2))

4. Haga clic en Formato y elija un color de resaltado; luego, haga clic en Aceptar y nuevamente en Aceptar para aplicar la regla.

Esto resaltará todas las celdas de su rango seleccionado cuyos valores sean los más cercanos al valor objetivo ubicado en E2.

Si trabaja con rangos extensos o experimenta resultados inesperados, asegúrese de que sus referencias sean correctas y de que las referencias absolutas o relativas estén configuradas según lo previsto (utilice el símbolo $ para bloquear la celda objetivo y las referencias del rango).