Coincidir y Coincidirx en Excel, cómo funcionan

Las funciones COINCIDIR y COINCIDIRX pertenecen al grupo de funciones de búsqueda y referencia de Excel y dan como resultado la posición relativa de un dato dentro de una matriz de referencia. Normalmente usaremos estas dos funciones anidadas con la función INDICE para hacer búsquedas de datos en matrices tal y como lo haríamos con las funciones BUSCARV, BUSCARH o BUSCARX, aunque le podemos encontrar otras utilidades. En cuánto a las diferencias entre las dos funciones, la función COINCIDIR es la primera que apareció en Excel y posteriormente apareció COINCIDIRX para incorporar alguna mejora, tal y como ha sucedido con la función BUSCARX y sus predecesoras BUSCARV y BUSCARH.

Cómo usar la función COINCIDIR

Empezaremos primero por la función CONICIDIR. Como decíamos, nos devuelve la posición relativa de un dato concreto en una matriz. Excel define literalmente esta función del siguiente modo: "Devuelve la posición relativa de un elemento en una matriz que coincide con un valor dado en un orden especificado". Es importante tener en cuenta que, como se dice en su definición, debemos decir a Excel qué orden sigue la matriz de datos si queremos que encuentre una coincidencia aproximada y no exacta, y esta es la limitación de esta función que elimina la función COINCIDIRX como veremos más adelante. Volviendo a la función COINCIDIR, sus argumentos son los siguientes:

• Valor Buscado: es el valor que se usa para encontrar el valor deseado en la matriz y puede ser un número, texto, valor lógico o una referencia a uno de ellos.
• Matriz buscada: es un rango contiguo de celdas que contienen posibles valores de búsqueda, una matriz de valores o una referencia a una matriz.
• Tipo de coincidencia: permite dar instrucciones a Excel para realizar la búsqueda. Es importante si sabemos que es posible que Excel no encuentre el valor exacto. En este argumento tenemos tres opciones: 
  • 1: La función COINCIDIR encontrará el valor más grande que sea menor o igual al valor buscado. La matriz de búsqueda debe estar ordenada de manera ascendente.
  • 0: La función COINCIDIR encontrará el primer valor que sea exactamente igual al valor buscado. La matriz de búsqueda puede estar en cualquier orden.
  • -1: La función COINCIDIR encontrará el valor más pequeño que sea mayor o igual que el valor buscado. La matriz de búsqueda debe estar ordenada de manera descendente.

Con este ejemplo entenderás cómo funciona cada argumento. En el rango A5:C8 tenemos una matriz de datos con las ventas de tres trabajadores de una frutería. En las celdas G5 y G6 tenemos dos funciones COINCIDIR cuyos argumentos tenemos en las dos celdas precedentes E5 y F5 y E6 y G6 respectivamente.

En el caso de la celda G5 tenemos los siguientes argumentos:

• Valor buscado: Estamos buscando el valor de la celda E5, es decir, 8. Al hacerlo como referencia de celda simplemente debemos cambiar el valor de la celda E5 para tener otros resultados, sin necesidad de escribir en la función.
• Matriz buscada: el valor buscado lo buscaremos en la columna de datos que contiene la cantidad, es decir, el rango B5:B8.
• Tipo de coincidencia: en este caso queremos una coincidencia exacta porque tenemos la certeza que el valor se encuentra en la matriz, por tanto, indicamos 0.

El resultado es 1 porque el 8 lo tenemos en la primera posición de la matriz. Hasta aquí bien, pero ahora vamos a jugar con el tipo de coincidencia para ver cómo opera la función si no tenemos la coincidencia exacta. En primer lugar, en la celda E5 pondremos un 7 como valor a buscar en la matriz, número que no se encuentra pero que tiene dos aproximaciones a la misma distancia, el 6 por debajo y el 8 por encima. Fíjate en que la columna cantidad está ordenada de forma descendente en la matriz. Veamos qué resultado obtenemos si ponemos tipo de coincidencia -1:

Con el tipo de coincidencia le estamos diciendo a Excel que la matriz está ordenada de forma descendente, así que nos devuelve la posición del número 8, que es el que encuentra primero como más aproximado. Si ponemos tipo de coincidencia 1 nos devolverá un 4 porque supondrá que el 8 está en la última posición de la matriz (orden ascendente).

Este es uno de los problemas de la función COINCIDIR, para coincidencias no exactas debemos tener ordenada la columna que hace de matriz en la que buscar. Fijaros en el siguiente ejemplo en el que la cantidad no está ordenada. Con tipo de coincidencia 1 obtenemos la posición 3, que es un 6:

Mientras que en tipo de coincidencia -1 nos devuelve error. Ambos resultados son incorrectos, lo que supone una gran limitación para esta función, como decíamos.

Cómo usar la función COINCIDIRX

Vamos a ver ahora a por COINCIDIRX. Es una función que apareció después de COINCIDIR precisamente para superar las limitaciones de ésta. Para empezar, los argumentos de esta función son sensiblemente diferentes:

• Valor buscado: este argumento no tiene cambios con respecto a la función COINCIDIR. Es el valor que queremos buscar en la matriz del siguiente argumento.
• Matriz buscada: igualmente sin cambios respecto a COINDICIR. Aquí pondremos la columna que contiene los datos que queremos analizar.
• Modo coincidencia: este campo es diferente. Especifica cómo se busca el valor buscado comparándolos con los valores de la matriz buscada y hace la misma función que el argumento "Tipo de coincidencia" de la función COINCIDIR.  Se trata de un argumento opcional en el que tenemos estas opciones:
  • 0: para coincidencia exacta. Es el valor por defecto si no ponemos nada.
  • 1: coincidencia exacta o el siguiente elemento mayor.
  • -1: coincidencia exacta o el siguiente elemento menor.
  • 2: Con caracteres comodín. Esta opción es muy interesante para hacer búsquedas en columnas con texto. Se podría buscar la referencia que empieza con "P" utilizando los comodines "*", por ejemplo, poniendo P* como valor buscado.
• Modo de búsqueda: es un campo adicional en el que indicamos a Excel cómo queremos que haga la búsqueda. Puede ir bien si tenemos una hoja de cálculo con muchos datos y queremos ahorrar tiempo a Excel para encontrar la información. Es también un argumento opcional, y tenemos estas opciones:
  • 1: Búsqueda del primero al último. Es la opción por defecto si no ponemos nada en este argumento.
  • -1: Búsqueda del último al primero. 
  • 2: Búsqueda binaria (orden ascendente)
  • -2: Búsqueda binaria (orden descendente)

Fíjate que ahora no es necesario indicar a Excel como está ordenada la matriz buscada, de hecho, no necesitamos tener la matriz ordenada, cosa que nos ahorrará bastantes problemas cuando trabajemos con coincidencias no exactas. Veamos algunos ejemplos para ver cómo opera COINCIDIRX. Seguiremos el mismo ejemplo anterior.

En el caso de la coincidencia exacta nos devuelve en ambos casos la posición 1. Hemos dejado la función sin el último argumento que sirve para indicar a Excel como queremos que haga la búsqueda porque tenemos una matriz muy pequeña. Si mantenemos el modo de coincidencia en 0 (exacta) y cambiamos a valores buscados que no están en la lista, la función nos devuelve error:

Si, con los mismos valores buscados, pasamos a tipo de coincidencia 1, para el caso del 7 nos devolverá la posición 1, que es un 8 porque le hemos pedido que si no encuentra el valor buscado, se posicione en el siguiente mayor. En cambio, para el caso de "Plátano" como valor buscado sigue arrojando error porque no encuentra nada parecido para comparar y devolver el siguiente mayor.

Si ponemos un -1, en el primer valor buscado, el 7, nos devolverá la posición 2, que en este caso sería el 6, es decir, el siguiente menor al que estamos pidiendo (7) porque Excel no lo está encontrando. En el caso de los Plátanos la función seguiría dando error, así que cambiaremos a búsqueda con caracteres comodín. En este caso cambiamos el valor buscado a P*, para que busque palabras que comienzan por P, tenga las letras que tenga después de la P, y modo de coincidencia 2 (caracteres comodín). Excel nos devuelve un 1, que es la posición en que se encuentra la única palabra de la matriz que comienza con P:

Para finalizar haremos un último ejemplo donde veremos cómo opera el último argumento. Imaginemos que tenemos dos productos que comienzan por P, Peras y Plátanos, el primero en la primera fila de la matriz y el segundo en última posición. Si utilizamos el tipo de coincidencia 2 y ponemos como valor buscado del producto "P*", los comenzados por P como en el ejemplo anterior, Excel nos devolverá resultados diferentes en función si ponemos 1 o -1 como argumento del modo de búsqueda. 

En el caso de 1, como empieza buscando del primero al último nos devolverá la posición 1, que es la de Peras y es la primera que encuentra Excel.

En cambio nos devolverá 4, es decir, Plátanos, si el modo de búsqueda es -1 porque le estamos pidiendo que empiece la búsqueda por el final.