Saltar al contenido principal

¿Cómo encontrar el valor más cercano o más cercano (número o cadena de texto) en Excel?

Supongamos que tiene una lista de números en una columna, y ahora debe encontrar el valor más cercano o más cercano a un valor dado de la lista de números. ¿Cómo lo afrontas? En realidad, puede encontrar el valor más cercano o el valor más cercano en Excel con los siguientes pasos.

Encuentra el número más cercano o más cercano con fórmula de matriz

Por ejemplo, tiene una lista de números en la Columna A, y ahora encontrará el valor más cercano o el valor más cercano de 18 de la Columna A. Puede hacerlo de la siguiente manera:

Seleccione una celda en blanco, ingrese la siguiente fórmula y presione el Ctrl + Shift + Enter llaves juntas.

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

Nota: En esta fórmula matricial de {=INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0))},

  • B3: B22 es el rango en el que desea encontrar el valor específico
  • E2 es el valor de búsqueda con el que desea compararlo.
cinta de nota ¿La fórmula es demasiado complicada para recordar? Guarde la fórmula como una entrada de texto automático para reutilizarla con un solo clic en el futuro.
Leer más ...     Prueba gratis

Seleccione fácilmente todos los números más cercanos en el rango de desviación del valor dado con Kutools para Excel

A veces, es posible que desee averiguar y seleccionar todos los valores de armario al valor dado en un rango. En realidad, podemos definir un valor de desviación y luego aplicar Kutools para Excel Seleccionar celdas especiales utilidad para averiguar y seleccionar todos los valores más cercanos dentro del rango de división de dar valor fácilmente.

Kutools for Excel- Incluye más de 300 útiles herramientas para Excel. Prueba gratuita de todas las funciones durante 60 días, ¡no se requiere tarjeta de crédito! Consíguelo ahora

Por ejemplo, en nuestro ejemplo definimos el valor de desviación como 2, y el valor dado es 18. Por lo tanto, necesitamos averiguar y seleccionar valores entre 16 (= 18-2) y 20 (= + 18 2). Consulte los siguientes pasos:

1. Seleccione el rango donde buscará los valores más cercanos al valor dado y luego haga clic en Kutools > Seleccione > Seleccionar celdas específicas.

2. En el cuadro de diálogo de apertura Seleccionar celdas específicas,
(1) Compruebe la Celular opción en el Tipo de selección sección;
(2) En el Tipo específico sección, haga clic en la primera lista desplegable y seleccione Mayor qué o igual a de él y escriba 16 en el siguiente cuadro, y luego seleccione Menos que o igual a de la segunda lista desplegable y escriba 20 en el siguiente cuadro. Ver captura de pantalla de la izquierda:

3. Haga clic en Ok para aplicar esta utilidad. Luego aparece un cuadro de diálogo que le muestra cuántas celdas se han seleccionado. Y verá que todos los valores más cercanos dentro del rango de desviación del valor dado se seleccionan como se muestra a continuación:


Encuentre la cadena de texto más cercana o más cercana con una herramienta útil

Si tiene Kutools para Excel instalado, puede aplicar su función de búsqueda difusa para encontrar fácilmente las cadenas de texto más cercanas de un rango determinado en Excel. Haz lo siguiente:

Kutools for Excel- Incluye más de 300 útiles herramientas para Excel. Prueba gratuita de todas las funciones durante 60 días, ¡no se requiere tarjeta de crédito! Consíguelo ahora

1. Hacer clic Kutools > Encuentre > Búsqueda difusa para habilitar el panel Búsqueda aproximada en su libro de trabajo.

2. En el panel de búsqueda aproximada, configure de la siguiente manera ;
(1) Compruebe la Especificado opción y seleccione el rango donde buscará las cadenas de texto más cercanas;
(2) Compruebe la Buscar por texto especificado opción;
(3) Vaya al Texto cuadro y escriba el texto especificado cuyas cadenas de texto más cercanas encontrará;
(4) En el Número máximo de caracteres diferentes cuadro, escriba un número. En mi caso, escribo 1;
(5) En La longitud de la cadena de celdas es al menos cuadro, escriba un número. En mi caso, escribo 5;

3. Haga clic en Encuentre del botón.

Ahora expanda los resultados de la búsqueda y verá que todas las cadenas de texto más cercanas se enumeran a granel. Ver captura de pantalla:


Demostración: seleccione todos los valores más cercanos en el rango de desviación del valor dado

Kutools for Excel: ¡Más de 300 herramientas útiles a tu alcance! Comience hoy su prueba gratuita de 30 días sin limitaciones de funciones. ¡Descargar ahora!

Las mejores herramientas de productividad de oficina

🤖 Asistente de IA de Kutools: Revolucionar el análisis de datos basado en: Ejecución inteligente   |  Generar codigo  |  Crear fórmulas personalizadas  |  Analizar datos y generar gráficos  |  Invocar funciones de Kutools...
Características populares: Buscar, resaltar o identificar duplicados   |  Eliminar filas en blanco   |  Combine columnas o celdas sin perder datos   |   Ronda sin fórmula ...
Super búsqueda: Búsqueda virtual de criterios múltiples    Búsqueda V de valores múltiples  |   VLookup en varias hojas   |   Búsqueda difusa ....
Lista desplegable avanzada: Crear rápidamente una lista desplegable   |  Lista desplegable dependiente   |  Lista desplegable de selección múltiple ....
Administrador de columnas: Agregar un número específico de columnas  |  Mover columnas  |  Toggle Estado de visibilidad de columnas ocultas  |  Comparar rangos y columnas ...
Características destacadas: Enfoque de cuadrícula   |  Vista de diseño   |   Gran barra de fórmulas    Administrador de hojas y libros de trabajo   |  Biblioteca de Recursos (Texto automático)   |  Selector de fechas   |  Combinar hojas de trabajo   |  Cifrar/descifrar celdas    Enviar correos electrónicos por lista   |  Súper filtro   |   Filtro especial (filtro negrita/cursiva/tachado...) ...
Los 15 mejores conjuntos de herramientas12 Texto Herramientas (Añadir texto, Quitar caracteres, ...)   |   50+ Tabla Tipos (Diagrama de Gantt, ...)   |   40+ Práctico Fórmulas (Calcular la edad según el cumpleaños, ...)   |   19 Inserción Herramientas (Insertar código QR, Insertar imagen desde la ruta, ...)   |   12 Conversión Herramientas (Números a palabras, Conversión de Moneda, ...)   |   7 Fusionar y dividir Herramientas (Filas combinadas avanzadas, Células partidas, ...)   |   ... y más

Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo.  Haga clic aquí para obtener la función que más necesita...

Descripción


Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
Comments (42)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do we do this if our data is filtered?
This comment was minimized by the moderator on the site
copy the filtered data to a new sheet
This comment was minimized by the moderator on the site
Using the formula how would you return the value next to 17 if there was another column next to number like names. So if 17 is the closest in rang the name next to 17 (John) would be returned?


Example: 18 is nearest to 17 so the return value would be John


Numbers Names
38 Tammy
17 John
20 Amy
This comment was minimized by the moderator on the site
You can use the Approximate match of VLOOKUP function to solve this problem.
=VLOOKUP(E2,A1:B15,2,TRUE)
This comment was minimized by the moderator on the site
I used this formula =INDEX(B3:B22,MATCH(MIN(ABS(B3:B22-E2)),ABS(B3:B22-E2),0)) and it works great. However i have found that where the source number is exactly between two numbers in the range, the lower range number is selected to be the closest.

eg: Searching for the closest number to 9 in the range: 6, 8, 10, 12. It will chose 8 instead of 10. Rounding convention is to round up if exactly half way between. Is there a workaround? Thanks.
This comment was minimized by the moderator on the site
ITS NOT WORKING its #N/A somehow
This comment was minimized by the moderator on the site
Hi DAKOT,
=INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-D1)),ABS(A1:A20-D1),0)) is an array formula, after entering it, please remember to press the Ctrl + Shift + Enter keys together.
This comment was minimized by the moderator on the site
Hi,
im using that formula in finding closest date, it is working.but i want to add condition: closest date that is less than 30 days of the current date (today).it is possible?
Anyone can help please?thank you
This comment was minimized by the moderator on the site
Hi farolito,
How about changing the value you will compare with to =TODAY() in Cell D1?
This comment was minimized by the moderator on the site
Hi, I can get it to work, amazing, BUT not when I input '1' as my 'match_type', instead of the '0' that you used. I want to return values less than or equal to, not just closest to +/-. If I enter 1 instead of 0, it doesn't work. Thoughts on why this might be?
This comment was minimized by the moderator on the site
Great formula -thank you-just a quick question. Anyone know how to highlight the cell that is closest in the match so in long lists it is easy to find??
This comment was minimized by the moderator on the site
If you have the row number from the formula above then you could set a conditional formatting rule on the search array to highlight a cell if it lies on that row.
This comment was minimized by the moderator on the site
It does not work for me! Excel says that there is an error :(
This comment was minimized by the moderator on the site
My bad This will look for all nearest date to today in column d =LARGE(D:D,COUNTIF(d:D,">="&TODAY())) http://WWW.excelireland.com
This comment was minimized by the moderator on the site
Hi, I tried the above formula. however, it is giving me a #N/A. 1. Copy pasted the range from A2 to A43. 2. Formula given was : =INDEX(A2:A43,MATCH(MIN(ABS(A2:A43-H1)),ABS(A2:A43-H1),0)) 3. Press control +Shift +enter Can anybody help me as to what wrong I am doing. :(
This comment was minimized by the moderator on the site
Replace the " , " separator for " ; "
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations