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.
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:
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:
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:
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:
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:
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