Búsqueda de múltiples criterios con ÍNDICE y COINCIDIR
Cuando se trabaja con una gran base de datos en una hoja de cálculo de Excel con varias columnas y títulos de fila, siempre es complicado encontrar algo que cumpla con varios criterios. En este caso, puede usar una fórmula matricial con las funciones ÍNDICE y COINCIDIR.
¿Cómo realizar una búsqueda con múltiples criterios?
Para encontrar el producto que es blanco y de tamaño medio con un precio de $18 como se muestra en la imagen anterior, puede aprovechar la lógica booleana para generar una matriz de 1s y 0s que muestre las filas que cumplen con los criterios. La función COINCIDIR luego encontrará la posición de la primera fila que cumple con todos los criterios. Después de eso, ÍNDICE encontrará el ID del producto correspondiente en la misma fila.
Sintaxis genérica
=ÍNDICE(rango_retorno,COINCIDIR(1,(valor_criterio1=rango_criterio1*valor_criterio2=rango_criterio2*(…),0))
√ Nota: Esta es una fórmula matricial que requiere que la introduzca con Ctrl + Shift + Enter.
- rango_retorno: El rango donde desea que la fórmula combinada devuelva el ID del producto. Aquí se refiere al rango de ID de productos.
- valor_criterio: Los criterios utilizados para localizar la posición del ID del producto. Aquí se refiere a los valores en las celdas h3, H5 y H6.
- rango_criterio: Los rangos correspondientes donde se enumeran los valores_criterio. Aquí se refiere a los rangos de color, tamaño y precio.
- tipo_coincidencia 0: Obliga a COINCIDIR a encontrar el primer valor que sea exactamente igual al valor_búsqueda.
Para encontrar el producto que es blanco y de tamaño medio con un precio de $18, copie o ingrese la siguiente fórmula en la celda H8, y presione Ctrl + Shift + Enter para obtener el resultado:
=ÍNDICE(B5:B10,COINCIDIR(1,("Blanco"=C5:C10)*("Medio"=D5:D10)*(18=E5:E10),0))
O, use una referencia de celda para hacer que la fórmula sea dinámica:
=ÍNDICE(B5:B10,COINCIDIR(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
Explicación de la fórmula
=ÍNDICE(B5:B10,COINCIDIR(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): La fórmula compara el color en la celda h3 contra todos los colores en el rango C5:C10; compara el tamaño en H5 contra todos los tamaños en D5:D10; compara el precio en H6 contra todos los precios en E5:E10. El resultado inicial es así:
{VERDADERO;FALSO;VERDADERO;FALSO;VERDADERO;FALSO}*{FALSO;FALSO;VERDADERO;VERDADERO;VERDADERO;FALSO}*{FALSO;FALSO;FALSO;VERDADERO;VERDADERO;FALSO}.
La multiplicación transformará los VERDADEROs y FALSOS en 1s y 0s:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Después de la multiplicación, tendremos una sola matriz como esta:
{0;0;0;0;1;0}. - COINCIDIR(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0) = COINCIDIR(1,{0;0;0;0;1;0},0): El tipo_coincidencia 0 pide a la función COINCIDIR que encuentre la coincidencia exacta. La función luego devolverá la posición de 1 en la matriz {0;0;0;0;1;0}, que es 5.
- ÍNDICE(B5:B10B5:B10,COINCIDIR(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = ÍNDICE(B5:B10B5:B10,5): La función ÍNDICE devuelve el quinto valor en el rango de ID de productos B5:B12, que es 30005.
Funciones relacionadas
La función ÍNDICE de Excel devuelve el valor mostrado basado en una posición dada de un rango o una matriz.
La función COINCIDIR de Excel busca un valor específico en un rango de celdas y devuelve la posición relativa del valor.
Fórmulas relacionadas
Buscar valor de coincidencia más cercana con múltiples criterios
En algunos casos, es posible que necesite buscar el valor de coincidencia más cercana o aproximada basándose en más de un criterio. Con la combinación de las funciones ÍNDICE, COINCIDIR y SI, puede hacerlo rápidamente en Excel.
Coincidencia aproximada con ÍNDICE y COINCIDIR
Hay momentos en los que necesitamos encontrar coincidencias aproximadas en Excel para evaluar el rendimiento de los empleados, calificar las puntuaciones de los estudiantes, calcular el envío según el peso, etc. En este tutorial, hablaremos sobre cómo usar las funciones ÍNDICE y COINCIDIR para recuperar los resultados que necesitamos.
Buscar valores desde otra hoja de trabajo o libro
Si sabe cómo usar la función BUSCARV para buscar valores en una hoja de trabajo, buscar valores desde otra hoja de trabajo o libro no será un problema para usted. El tutorial le mostrará cómo buscar valores desde otra hoja de trabajo en Excel.
Las Mejores Herramientas de Productividad para Office
Kutools para Excel - Te Ayuda a Sobresalir Entre la Multitud
Kutools para Excel Presume de Más de 300 Funciones, Asegurando Que Lo Que Necesitas Está a Solo Un Clic de Distancia...
Office Tab - Habilita Lectura y Edición con Pestañas en Microsoft Office (incluye Excel)
- ¡Un segundo para cambiar entre decenas de documentos abiertos!
- Reduce cientos de clics de ratón para ti todos los días, di adiós al síndrome del túnel carpiano.
- Aumenta tu productividad en un 50% al ver y editar múltiples documentos.
- Trae Pestañas Eficientes a Office (incluye Excel), Al Igual Que Chrome, Edge y Firefox.