sábado, 20 de octubre de 2012

Las funciones INDICE y COINCIDIR combinadas para busquedas en Excel


Los que conocen la función BUSCARV (o su homóloga BUSCARH) saben que tiene una limitación (a pesar de ser de las más utilizadas en Excel). Ésta estriba en la incapacidad de obtener valores en una columna que esté más a la izquierda que la columna donde está el valor buscado. Es decir, no podemos poner un numero negativo en su tercer argumento núm_columna.

Esta contrariedad es salvable a costa de tener que anidar la función COINCIDIR dentro de la función INDICE, pero nos puede sacar de muchos apuros.

Tengamos esta tabla como referencia:




Supongamos que necesitamos buscar el nombre de un trabajador a partir de su fecha de nacimiento. Esto es, introducir la fecha en una celda y que en otra, nos apareciera el nombre de forma automática. Si bien con BUSCARV no sería posible hacerlo al estar la columna de búsqueda (Fecha nacimiento) más a la derecha de la columna objetivo (ID Nombre trabajador), si sería posible combinando INDICE y COINCIDIR de la forma que vemos en la imagen de la derecha.

En la barra de formulas hemos escrito las funciones anidadas, donde el resultado lo proporciona la función INDICE, eligiendo entre la columna "ID Nombre trabajador", aquel que está situado en la 6ª fila.

Para no tener que editar constantemente la fórmula para modificar el segundo argumento de la función INDICE con un nuevo nº de fila cada vez que queramos buscar otro trabajador con otra fecha de nacimiento, en su lugar hemos introducido la función COINCIDIR... que nos proporciona ese nº de fila que ocupa la fecha de nacimiento que introducimos de forma más cómoda y sostenible en la celda C13.


La función COINCIDIR tiene tres argumentos:

=COINCIDIR (valor_buscado;matriz_buscada;[tipo_de_coincidencia])

Valor_buscado = es la celda C13 porque allí escribiremos la fecha que debe buscar la función COINCIDIR.
Matriz_buscada = es la matriz donde se buscará la fecha introducida en C13
[Tipo_de_coincidencia] = Es opcional (por eso no lo vemos en la imagen anterior). Si se pone el número 1 o se omite, al buscar la fecha, si no encontrara una coincidencia exacta, tomaría la más cercana por abajo. Si se pone un 0, obligamos a que haya una coincidencia exacta. Si se pone el número -1 tomaría la fecha más cercana por arriba.

Por todo ello, si pusiéramos de forma aislada la función COINCIDIR:  =COINCIDIR (C13;C3:C10) , nos devolvería el número 6 en el ejemplo anterior (que es el número que utiliza INDICE en su segundo argumento para buscar el nombre del trabajador).

Si desea llevar a la práctica todo lo que le hemos ofrecido en este artículo, puede asistir a nuestros cursos prácticos de Excel o puede solicitarlos in company:









21 comentarios:

  1. La mejor y más clara explicación de como funcionan las funciones de INDICE y COINCIDIR combinadas.

    GRACIAS!

    ResponderEliminar
  2. Me funciona bien con matrices pequeñas, pero no con grandes. Puede ser?

    ResponderEliminar
  3. amigo que pasa con aquellos datos que estan repetidos o son iguales?

    ResponderEliminar
    Respuestas
    1. Devuelve el primero que encuentra. Estas funciones son adecuadas para búsquedas de valores únicos.

      Eliminar
  4. sabe alguien si se puede la función índice+coincidir pero con condición si

    lo que quiero qie me vaya a buscar lo que le voy a pagar un trabajador pero que sea de la empresa que yo quiera por ejemplo:

    índice-lo que le voy a pagar -coincidir el código del empleado , pero que sea de la emplesa Pepsi ejemplo

    ResponderEliminar
  5. Alguna solucion para datos que se repiten

    ResponderEliminar
  6. Una Concion usando otra referencia como la fecha o otro identificador
    ,

    ResponderEliminar
  7. si escribo la fecha 01/01/1965 me muestra Gómez Lis, Belen. y no es correcto.
    alguna solución???

    ResponderEliminar
    Respuestas
    1. Incluye un cero después del rango del COINCIDIR para que el resultado sea el exacto.

      =INDICE(A2:A10, COINCIDIR(B14, B2:B10,0))

      Saludos.

      Eliminar
  8. Buenas noches, pido su ayuda para vaciar en un formulario los datos del personal, dependiendo de la respuesta de inputbox al pedir clave_empleado. Mis datos Alejandro Ariza aarizag@yahoo.com.mx

    ResponderEliminar
  9. Hola.
    Me funciona perfectamente, pero lo utilizo para hacer una factura automática en función del nombre del cliente y la fecha. Lo malo es que al arrastrar, me repite siempre los datos. Hay manera de filtrar eso y que no repita para que la factura la haga automática??
    Muchísimas gracias por el aporte.

    ResponderEliminar
    Respuestas
    1. Habría que er la hoja de cálculo y la fórmula que has introducido. ¿has revisado los signos dolar para fijar o no fijar adecuadamente las fórmulas?
      En este blog hay un artículo referido a las referencias relativas y absolutas.
      Saludos.

      Eliminar
    2. Muchísimas gracias por tu respuesta. Sí, utilizo el $ pero la fórmula no discrimina si ya ha leído una línea, entonces siempre muestra la misma línea, una y otra vez.
      La fórmula es esta:
      {=INDICE(descripciones;COINCIDIR($G$6&$B$14;fechas&apellidos;0))}
      donde G6 y B14 son la fecha y el apellido del comprador, si es campos los encuentra en la hoja de ventas (donde están creados los rangos) los muestra, pero al arrastrar hacia abajo, lo repite tantas veces como celdas arrastre.

      Buscaré las referencias relativas que dices.
      Mil gracias de nuevo.

      Eliminar
    3. Este comentario ha sido eliminado por el autor.

      Eliminar
    4. Quítale los signos $ a $G$6&$B$14

      Si no funciona, envíame el archivo a info@temporaconsultores.com

      Eliminar
  10. Y como sería para que busque un valor pero me regrese el valor de la celda que se encuentre arriba?

    hasta ahora solo he visto como sería pero en version Ingles, quise replicarla de forma similar pero no lo logro :(

    ResponderEliminar
    Respuestas
    1. Si utilizas la función COINCIDIR, solo tienes que restar 1 al resultado que te ofrezca (con el tercer argumento = 0). De esta forma, la función ofrece el valor que buscas.... no.... el de arriba.
      Saludos

      Eliminar