Determinar si un valor existe en un rango de Excel

En ocasiones necesitamos determinar si un valor existe en un rango de Excel utilizando fórmulas de manera que podamos tomar una decisión posterior en base al resultado. Analizaremos varias alternativas y podrás elegir la que se adapte mejor a tus necesidades.

Como ejemplo utilizaremos el rango A1:E11 que contiene los nombres de diferentes ciudades del mundo y el valor que deseamos saber si existe en dicho rango lo colocaré en la celda H1.

Determinar si un valor existe en un rango de Excel

Solución con la función O

La primera alternativa que podemos utilizar es la función O la cual evalúa todos sus argumentos para devolvernos el valor VERDADERO si al menos una expresión es verdadera. De esta manera, si pedimos a Excel que compare cada celda del rango con el valor buscado y al menos el valor de una de ellas es igual, entonces sabremos si el valor existe dentro del rango. Considera la siguiente fórmula:

=O(A1:E11=H1)

Esta fórmula debe ser introducida como una fórmula matricial por lo que debemos pulsar la combinación de teclas Ctrl + Mayús + Entrar. Excel realizará la comparación de cada celda del rango y si al menos una celda es igual nos devolverá VERDADERO como resultado:

Confirmar si existe un valor dentro de un rango en Excel

De esta manera la función O nos ayuda a determinar si un valor existe en un rango de Excel, pero solamente obtendremos como resultado el valor VERDADERO o FALSO sin importar cuántas veces aparezca el valor dentro del rango.

Solución con la función CONTAR.SI

Una alternativa que me agrada un poco más que la anterior es utilizar la función CONTAR.SI ya que no solamente sabremos si el valor existe dentro del rango sino que conoceremos la cantidad de veces que aparece. La fórmula que debemos utilizar es muy sencilla:

=CONTAR.SI(A1:E11, H1)

Como resultado obtendremos el número de veces que aparece el valor dentro del rango:

Establecer si determinado valor existe o no en un rango de Excel

Si el valor de la fórmula es cero, quiere decir que el valor no existe dentro del rango, de lo contrario obtenemos la cantidad exacta de apariciones. Esto nos da un poco más de información sobre la existencia del valor dentro del rango sin embargo no podemos saber la ubicación exacta de dicho valor.

Validar existencia con una macro

Si las alternativas anteriores no son suficientes para solucionar tu problema, sino que necesitas conocer la celda donde se encuentra el valor buscado, entonces es necesario crear una macro que nos ayude a marcar las celdas donde se encuentra el valor. Considera el siguiente código VBA de la subrutina ValorExiste:

Sub ValorExiste()

'Definición de variables
Dim rango As String
Dim valor As String
Dim resultado As Range
Dim primerResultado As String
Dim cont As Integer

'Solicitar información al usuario
rango = InputBox("Ingresa el RANGO a buscar:")
valor = InputBox("Ingresa el VALOR a buscar:")

'Inicializar contador de coincidencias
cont = 0

'Primera búsqueda del valor dentro del rango
Set resultado = Range(rango).Find(What:=valor, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)
                    
'Si el resultado de la búsqueda no es vacío
If Not resultado Is Nothing Then
    primerResultado = resultado.Address
    
    'Inicia bucle para hacer varias búsquedas
    Do
        cont = cont + 1
        
        'Cambia el color de fondo de la celda
        resultado.Interior.ColorIndex = 6
        
        'Vuelve a buscar el valor
        Set resultado = Range(rango).FindNext(resultado)
        
    Loop While Not resultado Is Nothing And _
        resultado.Address <> primerResultado
End If

'Muestra un cuadro de diálogo con el número de coincidencias
MsgBox "Se encontraron " & cont & " coincidencias."

End Sub

Este código comienza solicitando al usuario el rango sobre el cual se realizará la búsqueda así como el valor a buscar. Es entonces que hacemos uso de la función VBA llamada Find la cual nos ayuda a realizar la búsqueda del valor sobre el rango indicado.

En caso de que la función Find encuentre una celda que contenga dicho valor, devolverá su dirección. Es por eso que implementamos un bucle Do … Loop While para seguir buscando el valor dentro del rango hasta que ya no se encuentre más. Cada vez que encontramos un valor utilizamos la propiedad ColorIndex para marcar la celda con un color de manera que podamos identificar fácilmente la celda encontrada.

Dentro del bucle coloqué una variable encargada de llevar la cuenta de las veces que encontramos el valor buscado de manera que al final de la subrutina podamos mostrar un mensaje al usuario indicando el número de coincidencias.

Prueba de la macro

Para probar la macro debo pulsar el botón Macros de la ficha Programador y elegir el nombre de la subrutina a ejecutar. Al pulsar el botón Ejecutar se mostrará el primer cuadro de dialogo de la macro solicitando el rango donde se realizará la búsqueda:

Macro para determinar si un valor existe en un rango de Excel

Una vez proporcionado el rango de búsqueda pulsamos el botón Aceptar y se mostrará un nuevo cuadro de diálogo solicitando el valor a buscar:

Buscar valores en un rango de Excel

Al pulsar el botón Aceptar la macro realizará la búsqueda y cambiarla el color de fondo de las celdas que contengan el valor buscado. Además mostrará el cuadro de diálogo indicando el número de coincidencias encontradas:

Macro para buscar un valor dentro de un rango en Excel

De esta manera, la macro encuentra y resalta las celdas que contienen el valor buscado y además envía una notificación indicando la cantidad de coincidencias encontradas.

Con los tres métodos  presentados en este artículo tendrás la posibilidad de elegir la solución que mejor se adapte a tus necesidades y poder determinar si un valor existe en un rango de Excel. Descarga el libro de trabajo que contiene los ejemplos y código de la macro desarrollados.

Artículos relacionados
Buscar un valor y regresar múltiples resultados

Moisés Ortiz

Autor: Moisés Ortiz

Moisés es consultor en tecnologías de la información con especialidad en herramientas de inteligencia de negocios y análisis de datos. Tiene más de 25 años de experiencia en el uso de Excel y es una de sus herramientas predilectas para la creación de reportes. Ver más ➤