Valor existente en un rango de Excel

Compatibilidad: Truco compatible con Excel 365Truco compatible con Excel 2016Truco compatible con Excel 2013Truco compatible con Excel 2010Truco compatible con Excel 2007

Podemos comprobar si disponemos de un valor existente en un rango de Excel.

En el rango A1:C7, disponemos de las provincias de nuestros clientes y deseamos buscar si el valor de F1 se encuentra incluido en el rango.
Valor existente en un rango de Excel: Rango de búsqueda A1:C7 y valor repetido en F1.
  • EL VALOR EXISTE (Método 1). Devuelve VERDADERO si el valor está repetido y FALSO si no lo está.

Emplearemos la siguiente función:

- Función O. Devuelve VERDADERO si algún argumento es VERDADERO.

  1. Ir a: Celda B11 / Escribir:

    =O(A1:C7=F1)
  2. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=O(A1:C7=F1)}.

Valor existente en un rango de Excel: Celda B11 con fórmula {=O(A1:C7=F1)}, mostrando VERDADERO
  • EL VALOR EXISTE (Método 2). Devuelve REPETIDO si el valor está repetido y NO REPETIDO si no lo está.

Emplearemos las siguientes funciones, anidándolas:

- Función SI. Especifica un texto lógico para ejecutar.

- Función O. Devuelve VERDADERO si algún argumento es VERDADERO.

  1. Ir a: Celda B12 / Escribir:

    =SI(O(A1:C7=F1);"REPETIDA";"NO REPETIDA")
  2. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=SI(O(A1:C7=F1);"REPETIDO";"NO REPETIDO")}.

Valor existente en un rango de Excel: Celda B12 con fórmula {=SI(O(A1:C7=F1);"REPETIDA";"NO REPETIDA"))}, mostrando REPETIDA
  • EL VALOR EXISTE (Método 3). Devuelve REPETIDO si el valor está repetido y NO REPETIDO si no lo está.

Emplearemos las siguientes funciones, anidándolas:

- Función SI. Especifica un texto lógico para ejecutar.

- Función SUMAPRODUCTO. Devuelve la suma de los productos de los componentes de la matriz correspondiente.

  1. Ir a: Celda B13 / Escribir:

    =SI(SUMAPRODUCTO(--(A1:C4=F1))>=1;"REPETIDO";"NO REPETIDO")
  2. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=SI(SUMAPRODUCTO(--(A1:C4=F1))>=1;"REPETIDO";"NO REPETIDO")}.

Valor existente en un rango: Celda B13 con fórmula =SI(SUMAPRODUCTO(--(A1:C4=F1))>=1;"REPETIDA";"NO REPETIDA"), mostrando REPETIDA
  • EL VALOR EXISTE EN UNA COLUMNA. Devuelve REPETIDO si el valor está repetido y NO REPETIDO si no lo está.

Emplearemos las siguientes funciones, anidándolas:

- Función ESERROR. Devuelve VERDADERO si el valor es cualquier valor de error.

- Función BUSCARV. Busca en la primera columna de una matriz y se mueve en la fila para devolver el valor de una celda.

  1. Ir a: Celda B14 / Escribir:

    =SI(ESERROR(CONSULTAV(F1;A1:C7;1;FALSO));"NO REPETIDO";"REPETIDO")
Valor existente en un rango de Excel: Celda B14 con fórmula =SI(ESERROR(CONSULTAV(F1;A1:C7;1;FALSO));"NO REPETIDA";"REPETIDA"), mostrando REPETIDA
  • EL VALOR EXISTE ES IDÉNTICO. Devuelve VERDADERO si el valor está repetido y es idéntico y FALSO si no está repetido y no ese idéntico (mayúscula y minúscula).

Emplearemos las siguientes funciones, anidándolas:

- Función O. Devuelve VERDADERO si algún argumento es VERDADERO.

- Función IGUAL. Comprueba si dos valores de texto son exactamente iguales.

  1. Ir a: Celda B16 / Escribir:

    =O(IGUAL(A1:C7;F1))
  2. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=O(IGUAL(A1:C7;F1))}.

Valor existente en un rango: Celda B16 con fórmula {=O(IGUAL(A1:C7;F1))}, mostrando VERDADERO
  • NÚMERO DE REPETIDOS. Devuelve el número de valores distintos repetidos.

Emplearemos la siguiente función:

- Función CONTAR.SI. Cuenta el número de celdas que no están en blanco dentro de un rango que coincida con los criterios especificados.

  1. Ir a: Celda B18 / Escribir:

    =CONTAR.SI(A1:C7; F1)
Valor existente en un rango: Celda B18 con fórmula =CONTAR.SI(A1:C7; F1), mostrando 2.
  • VALORES NO REPETIDOS. Devuelve el número de valores no repetidos en el rango.

Emplearemos las siguientes funciones, anidándolas:

- Función SUMA. Suma todos los números en un rango de celdas.

- Función CONTAR.SI. Cuenta el número de celdas que no están en blanco dentro de un rango que coincida con los criterios especificados.

  1. Ir a: Celda B20 / Escribir:

    =SUMA(1/CONTAR.SI(A1:C7;A1:C7))
  2. Pulsar la tecla F2, para editar la fórmula / Pulsar la tecla Control y mantenerla pulsada / Pulsar la tecla Mayús. y mantener las 2 teclas pulsadas / Pulsar la tecla Enter. Insertará la fórmula entre llaves como matricial {=SUMA(1/CONTAR.SI(A1:C7;A1:C7))}.

Valor existente en un rango: Celda B20 con fórmula {=SUMA(1/CONTAR.SI(A1:C7;A1:C7))}, mostrando 17.
  • NÚMERO DE FILA PRIMER VALOR REPETIDO. Devuelve el número de de la fila del primer valor encontrado.

Emplearemos la siguiente función:

- Función SIERROR. Devuelve un valor que se especifica si una fórmula lo evalúa como un error; de lo contrario, devuelve el resultado de la fórmula.

- Función COINCIDIR. Busca los valores en una referencia o matriz.

  1. Ir a: Celda B23 / Escribir:

    = SIERROR(COINCIDIR(F1;A1:A7;0); SIERROR(COINCIDIR(F1;B1:B7;0); SIERROR(COINCIDIR(F1;C1:C7;0); "NO REPETIDA")))
Valor existente en un rango: Celda B22 con fórmula = SIERROR(COINCIDIR(F1;A1:A7;0);SIERROR(COINCIDIR(F1;B1:B7;0);SIERROR(COINCIDIR(F1;C1:C7;0);"NO REPETIDA"))), mostrando 1

Así en Excel podemos comprobar si un valor existe en un rango.

Nivel de dificultad: Avanzado Excel Avanzado

12 comentarios en “Valor existente en un rango de Excel

  • Muchas gracias por tan buenos ejemplos, estoy algo confundido con un cálculo que quiero hacer, creo que con algunas de estas funciones puede funcionar aunque aún no preciso como, el problema es el siguiente:

    Quiero calcular en una nueva celda algún valor que me permita identificar si un cliente tiene dualidad de status, es decir, si nos compra "Al Detal y Al Mayor", y también poder identificar solo los que nos compran "Solo Detal" y los que nos compran "Solo Al Mayor". El ejemplo es el siguiente:

    A2=Cliente A
    A3=Cliente A
    A4=Cliente B
    A5=Cliente C
    A6=Cliente D
    A7=Cliente D

    B2=Al Detal
    B3=Al Mayor
    B4=Al Mayor
    B5=Al Detal
    B6=Al Detal
    B7=Al Detal

    C2= "Es la celda donde quiero identificar el status del cliente, para el cliente A deberia ser "Al Detal y Al Mayor" y así sucesivamente para el resto)

    De antemano gracias,

    saludos

    • Hola Eduardo,

      Faltaría algo de información...

      Necesitamos disponer de un listado con la asignación a cada cliente, como origen de datos (por ejemplo):
      Cliente A Detal / Mayor
      Cliente B Mayor
      Cliente C Detal
      Cliente D Detal

      Y en la columna B aplicar la fórmula BuscarV sobre el nombre del cliente.

      Saludos.

  • Intento usar la primera opcion en Excel 2003 (si, lo sé, es viejo pero no puedo actualizarlo)
    Cuando lo hago contra texto, funciona de maravillas, sin embargo, cuando intento aplicarlo con numeros, no resulta, dando error #N/A. Ya verifiqué que ambos numeros estén almacenados como número y no como texto (usando Convertir texto en columnas y el formato de celdas).

    PS: El problema es que entre la lista a verificar existen valores #N/A, producto de un BuscarV sin resultados. Por lo tanto, se debe verificar no existencia de errores en la matriz a buscar antes de ejecutar la busqueda de forma matricial. Problema resuelto por mi mismo. =)

  • Muy buena solución pero en mi caso estoy buscando nombres que se repitan y veo que el nombre debe ser igual porque tiene en cuenta la ortografía (tildes).

    • Hola Maritza,

      En Excel siempre se consideran diferentes las tildes, espacios, símbolos, etc.

      Prueba a anidar una condición más, es decir una para Evaluar por ejempl Excel y otra para Escel.

      Saludos.

  • Excelentes ejemplos. Muchas felicidades.

    Disculpe, estoy generando una base de datos en excel, la cual estoy buscando una formula que me permita evaluar un rango de celdas, que determine, si hay datos escritos y como resultado me permita escribir la palabra "SI", en una celda como punto revisión y en el caso contrario, no aceptar la palabra "SI" ,hasta que el rango de celdas seccionadas por la formula verifique la captura completa. .Utilizando la herramienta validación de datos.

    Agradezco su atención a su pronto respuesta.

  • NO PUEDO DETERMINAR LA FORMULA PARA OBTENER EL VALOR DE LOS BTU DEPENDIENDO DEL VALOR QUE OBTENGO EN EL RANGO QUE SE ESTABLECE PARA LA CAPACIDAD DEL BTU QUE DEBEN CORREPONDERLE

    AREA DEL LUGAR BTU QUE LE APLICAN
    53 M2 "X"

    de hasta BTU
    0 7 2600
    7 9 3350
    9 14 5200
    14 19 6000
    19 23 6900
    23 28 7500
    28 33 7900
    33 37 9000
    37 47 10900
    47 56 12800
    56 75 14900
    75 84 17000
    84 93 18000
    93 117 24900
    117 140 28900
    140 163 35800
    163 187 36100
    187 210 42900
    210 233 46900
    233 257 53800
    257 280 54000
    280 303 64700

  • Hola! Tengo una lista de 500 filas, que tengo que comparar con un rango de 11 (de M2 a M12).
    Esto aplicando esto: Estoy aplicando esto =O($M$2:$M$12=$A2)
    Pero cuando excede la fila 12 empieza a dar error... por lo que veo sólo las compara si están a la misma altura. Tengo excel 2007 (lo de las llaves por alguna razón tampoco me funciona).
    Sabrán decirme dónde está el error?

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.