FÓRMULAS PARA INDICAR SI UN VALOR EXISTE EN UN RANGO

Hola a todos:

Aunque en esta web suelo publicar muchos contenidos basados en programación VBA, también me gusta dar protagonismo a las fórmlas de Excel. Y es que en muchas (muchísimas) ocasiones, una fórmula puede ser más efectiva que usar código, ya sea por tiempo, por hábito, etc. El saber utilizarlas y combinarlas, hacen que sean una alternativa al uso de macros (aunque para algunos ejercicios concretos solo la programación nos puede ayudar).

Por lo tanto, hoy me gustaría contestar a la pregunta: ¿Qué fórmulas puedo usar para indicar que un valor está en un rango determinado de celdas?.

Pues bien, existen varias fórmulas para hacerlo, algunas con restricciones como que solo funcionan con datos numéricos o que solo es posible utilizar una columna como rango de búsqueda y otras que realizar el trabajo sin los límites comentados.

Aunque seguramente podría desarrollar otras fórmulas más complejas, finalmente me quedo con 9 métodos para realiza este trabajo. Vamos a ver en detalle cada una de ellos 🙂

Imaginad que tenéis dos columnas, una denominada RANGO y otra denominada DATO, y lo que queremos es utilizar una función que nos permita indicar cuáles son los valores de la columna DATO que se encuentran en la columna RANGO y en caso de existir coincidencia pondremos un «SI» y si no están un «NO» en la columna FÓRMULA:

También os especificaré si el rango de búsqueda puede aplicarse en varias columnas o únicamente funciona seleccionando una única columna.

Para el primer ejemplo vamos a utilizar las funciones CONTAR.SI y SI, que nos van a permitir contar las veces que el valor buscado se repite en la columna RANGO por lo tanto, si es mayor que cero indicamos «SI», de lo contrario será «NO». El rango de búsqueda pueden ser varias columnas:

=SI(CONTAR.SI($A$4:$A$11;B4)>0;"SI";"NO")

En el segundo ejemplo vamos a usar las función COINCIDIR, SI y SI.ERROR de manera que si la posición del valor buscado al coincidir en el rango de búsqueda es mayor que cero indicará que existe y lo marcamos como un «SI», de los contrario (si es un error) será «NO». El rango de búsqueda debe ser una única columna:

=SI.ERROR(SI(COINCIDIR(F4;$E$4:$E$11;0)>0;"SI");"NO")

En el tercer ejemplo vamos a usar las funciones BUSCARV, SI y ESERROR. Usando BUSCARV buscamos el valor de DATO en la columna rango, si devuelve el mismo valor es que existe, por lo tanto indicamos un «SI», si lo que devuelve es un error, marcamos un «NO». El rango de búsqueda debe ser una única columna:

=SI(ESERROR(BUSCARV(J4;$I$4:$I$11;1;0));"NO";"SI")

En el cuarto ejemplo vamos usar las funciones SUMAPRODUCTO y SI. De forma que si la función SUMAPRODUCTO nos devuelve un valor mayor a 0 marcaremos «SI», de lo contrario marcamos un «NO». El rango de búsqueda pueden ser varias columnas:

=SI(SUMAPRODUCTO(($M$4:$M$11=N4)*1)>0;"SI";"NO")

En el quinto ejemplo vamos a usar las funciones SUMAR.SI y SI. La función SUMAR.SI en el momento que encuentre coincidencia sumará las veces que se repite dicho valor, si es mayor que 0 marcaremos que «SI», en caso contrario marcamos «NO». Esta función solo es válida si trabajamos con datos numéricos y permite que el rango de búsqueda puedan ser varias columnas:

=SI(SUMAR.SI($Q$4:$Q$11;R4;$Q$4:$Q$11)>0;"SI";"NO")

Como habéis visto, combinamos varias funciones para optener el resultado esperado. Al tener que codificar un «SI» o un «NO» (o cualquier indicativo que estimemos oportuno) nos fuerza a usar el SI condicional, que se usa en todas las funciones, y como algunas fórmulas arrojan error cuando no encuentran el dato, debemos usar las funciones ESERROR y SI.ERROR para controlar el error y codificar el valor que necesitamos.

Además de estas funciones, existe otra variante con funciones matriciales que también nos pueden ofrecer el mismo resultado. Os muestro cuatro posibilidades (las de rojo) y os recuerdo que las matrices se introducen: seleccionando la celda que contiene la fórmula, pulsando en F2, seleccionamos la fórmula y luego presionamos CTRL + MAYUS + ENTRAR

El sexto ejemplo es una fórmula matricial, usamos las funciones SI y O, determinando que si el valor buscado existe en el rango indicaremos un «SI» y en caso contrario un «NO». Permite que el rango de búsqueda puedan ser varias columnas:

{=SI(O($A$17:$A$24=B17);"SI";"NO")}

El séptimo ejemplo es una variante de la fórmula matricial anterior, solo que combinamos con la función IGUAL, lo que nos va a permitir distinguir mayúsculas de minúsculas para búsquedas más exhaustivas. Permite que el rango de búsqueda puedan ser varias columnas:

{=SI(O(IGUAL($E$17:$E$24;F17));"SI";"NO")}

El octavo ejemplo es una fórmula (matricial) en la que usamos las funciones SI y SUMA de forma que sumamos las veces en las que aparece el valor en el rango de búsqueda. Si el valor es diferente de cero será verdadero y codificaremos «SI», en caso contrario será falso y codificaremos «NO». Permite que el rango de búsqueda puedan ser varias columnas:

{=SI(SUMA(($I$17:$I$24=J17)*1);"SI";"NO")}

La novena y última fórmula (matricial) combina las funciones SI y CONTAR. Cuando el valor que arroje la función sea mayor que cero indicará que el valor existe y por lo tanto marcamos un «SI», en caso contrario un «NO». Esta función solo es válida si trabajamos con datos numéricos y permite que el rango de búsqueda puedan ser varias columnas:

{=SI(CONTAR(SI(($M$17:$M$24=N17);$M$17:$M$24))>0;"SI";"NO")}

Como habéis podido observar, existen bastantes alternativas para saber si un valor está en un rango determinado, cada una con sus especificaciones para que podáis adaptarlas a vuestros trabajos y proyectos.

Por otra parte, en todos los casos podéis prescindir de las condiciones y las funciones de error para que veáis el resultado real que arroja la fórmula, de forma que algunas las podríais modificar para saber, por ejemplo, si el valor buscado se encuentra en el rango y si se repite varias veces (pero eso ya lo veremos en otro momento).

Es un post bastante extenso, pero creo que merece la pena hacerlo 🙂

Os dejo un archivo con el ejemplo de todas las fórmulas vistas y un resumen de algunas de sus características: