Búsqueda con múltiples criterios mediante ÍNDICE y COINCIDIR

Al trabajar con una base de datos grande en una hoja de cálculo de Excel con varias columnas y encabezados de fila, suele resultar complicado encontrar algo que cumpla varios criterios. En estos casos, puedes utilizar una fórmula matricial con las funciones ÍNDICE y COINCIDIR.

¿Cómo realizar una búsqueda con varios criterios?

Para encontrar el producto que sea blanco, de talla mediana y con un precio de 18 $, tal como se muestra en la imagen anterior, puede utilizar la lógica booleana para generar una matriz de unos y ceros que indique las filas que cumplen todos los criterios. La función COINCIDIR identificará entonces la posición de la primera fila que los satisface, y a continuación, ÍNDICE recuperará el ID de producto correspondiente en esa misma fila.

Sintaxis genérica

=INDEX()return_range,MATCH(1,())criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

√ Nota: Esta es una fórmula matricial que requiere pulsar Ctrl+Mayús+Intro.

Para encontrar el producto que sea blancoy de talla medianacon un precio de 18 $, copie o introduzca la siguiente fórmula en la celda H8 y pulse Ctrl+Mayús+Intropara obtener el resultado:

=ÍNDICE()B5:B10,COINCIDIR(1,())«Blanco»=C5:C10)*(«Mediano»=D5:D10)*(18=E5:E10),0))

O bien, utilice Una referencia de celda para hacer la fórmula dinámica:

=ÍNDICE()B5:B10,COINCIDIR(1,())H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

Explicación de la fórmula

=INDEX()B5:B10,MATCH(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 de la celda H3 con todos los colores del rango C5:C10; la talla de la celda H5 con todas las tallas del rango D5:D10; y el precio de la celda H6 con todos los precios del rango E5:E10. El resultado inicial es el siguiente:
  {VERDADERO;FALSO;VERDADERO;FALSO;VERDADERO;FALSO}*{FALSO;FALSO;VERDADERO;VERDADERO;VERDADERO;FALSO}*{FALSO;FALSO;FALSO;VERDADERO;VERDADERO;FALSO}.
  La multiplicación convierte los valores VERDADERO y FALSO en 1 y 0, respectivamente:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  Tras realizar la multiplicación, obtenemos una única matriz como esta:
  {0;0;0;0;1;0}.
• COINCIDIR(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=COINCIDIR(1,) El argumento tipo_coincidencia 0 indica a la función COINCIDIR que busque una coincidencia exacta. La función devuelve entonces la posición del valor 1 en la matriz {0;0;0;0;1;0}, que es 5.
• ÍNDICE()B5:B10,COINCIDIR(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = ÍNDICE(B5:B10La función ÍNDICE devuelve el 5.º valor del rango de ID de producto B5:B10, que es 30005.

Funciones relacionadas

Fórmulas relacionadas