INDICE y COINCIDIR con tres (o más) criterios de búsqueda
Hace un tiempo hice una publicación en la web sobre búsquedas en una tabla con las funciones INDICE y COINCIDIR, una alternativa muy buena a BUSCARV, sobre todo porque esta forma de buscar permite hacerlo hacia la izquierda (aunque con algunos ajustes BUSCARV también es capaz de hacerlo).
Las búsquedas en tablas de doble entrada suelen hacerse en base a dos criterios, uno vertical y uno horizontal. En muchos casos esto es suficiente para encontrar el valor deseado. Sin embargo pueden darse búsquedas más complejas que requieren algo más de ‘ingeniería de fórmulas’.
Imagina que tienes la siguiente tabla:
Te piden que halles las ventas realizadas del Producto C del almacén de Lugo y en el mes de abril.
Comencemos.
La función COINCIDIR busca un valor en una fila o columna y devuelve la posición en la que se encuentra. Por ejemplo si tienes los valores A, B y C y te piden buscar la letra B, la función devolverá el número 2 porque la B se encuentra en segundo lugar. Este resultado numérico es el que se necesita para decirle a la función INDICE en qué parte de la tabla se encuentra el valor buscado.
En el ejemplo de la imagen esta sería la fórmula a utilizar:
El resultado de la fórmula es 4 porque abril se encuentra en la cuarta posición en la matriz de búsqueda. La búsqueda con INDICE se hará en la columna 4.
Para buscar con COINCIDIR con dos criterios (en este ejemplo se busca el almacén de Lugo y el producto C) se hace necesaria la concatenación tanto del valor buscado como de la matriz de búsqueda.
Observa la fórmula y sobre todo fíjate dónde comienzan y terminan los rangos de la matriz:
=COINCIDIR(D2&D3;A9:A23&B9:B23;0)
D2&D3 es la expresión que concatena los valores de D2 y D3. Si introdujera la fórmula =D2&D3 el resultado sería ‘LugoProducto C’.
De igual forma se concatenan las matrices de búsqueda. La fórmula =A9:A23&B9:B23 da como resultado la matriz de la siguiente imagen:
Como puedes comprobar el resultado del valor buscado y el de la matriz de búsqueda tienen el mismo formato por tanto ya es posible la función INDICE para ello.
=INDICE(C9:N23;G4;G3)
- C9:N23 es el rango en el que se hará la búsqueda.
- G4 es la fila a devolver dentro del rango en la que se encuentra el valor buscado.
- G3 es la columna dentro del rango en la que se encuentra el valor buscado.
Si en la imagen anterior cuentas 8 filas y 4 columnas en el rango con el borde azul, obtendrás el valor 2.033,99, que es el resultado de la fórmula final.
Para no ocupar espacio innecesario en la hoja con los resultados parciales, se sustituyen las referencias G4 y G3 en la fórmula principal, por el contenido de las celdas referenciadas.
La fórmula final es la siguiente:
=INDICE(C9:N23;COINCIDIR(D2&D3;A9:A23&B9:B23;0);COINCIDIR(D4;C8:N8;0))
Parece complicada pero si la construyes de la misma forma en la que lo he hecho aquí, verás que es fácil.
Por favor, déjame un comentario si te ha parecido útil y comparte la publicación con quien pienses que la podría necesitar.
“Has ingresado de masiados argumentos para esta función”, la copié tal cuál tu 🙁
Hola Pao,
Estoy “casiseguro” de que utilizas la coma como separador de argumentos, por eso te aparece ese error. Cambia los puntos y comas por comas y verás cómo funciona.
busco algo similar pero en lugar de concatenar debe buscar y arrojar verdadero solo si uno de los dos coincide, o sea, cual es el conector OR en excel
La función lógica es O
No me resulta la formula, en mi caso debo cambiar punto y coma por coma, me arroja resultado :#¡valor!
Si se escribe de esta forma no genera error
=INDICE(C9:N23;Y(COINCIDIR(D2;A9:A23;0);Coincidir(D3;B9:B23;0;COINCIDIR(D4;C8:N8;0))
Me ha ayudado mucho, muchas gracias
Muy útil muchas gracias, yo lo he hecho agregando una nueva columna juntando las 2 condiciones con =a1&b1 y usando buscarv sobre la nueva columna, pero con esto no tengo que crear una columna auxiliar
me salvaste la vida
la formula coincidir quiero con varias hojas 3 a mas hojas
Buenas,
Utilice la función “=si” para determinar el valor asignado dependiendo el porcentaje obtenido por cada persona, ejemplo: =SI(F5>=97%;16;SI(F5>=91%;15;SI(F5>=85%;14;SI(F5>=79%;13;SI(F5>=72%;12). Luego de obtener dicho valor, necesito sumar todos estos valores para totalizar pero excel no me perminte sumar los mismo de ninguna manera.
Si alguien sabe una forma o conoce alguna manera se los agradeceria.
Saludos.
Magnífico y aprovecho una consulta para avanzar en conocimientos.
La función INDICE nos muestra el valor que cumple con una serie de condiciones. Pero si hay varios valores que cumplen con las condiciones, siempre toma el primero que se encuentra. ¿Cómo podría pedirle que me mostrara el mayor de ellos, por ejemplo?
Gracias
=COINCIDIR(D2&D3;A9:A23&B9:B23;0)
Para que lo tomen en cuenta: el concatenar celdas o rangos para referencias dentro de una formula, en algunos casos como para esta formula de Coincidir NO FUNCIONA para ciertas versiones de Excel. Lo probé en Excel 2016 y anteriores y no funcionaba, solamente en Office 365 se logra.
=INDICE(C9:N23;COINCIDIR(D2&D3;INDICE(A9:A23&B9:B23;);0);COINCIDIR(D4;C8:N8;0))
Para que funcione le formula en Excel 2016 y anteriores, se debe de modificar un poco la formula final. En la concatenación de la “matriz” de la primera formula anidada de “COINCIDIR” debe de hacerse por medio de la otra fórmula = “INDICE” para pueda funcionar en esas versiones de Excel.