Fórmula Excel INDEX MATCH con múltiples criterios

Excel ÍNDICE COINCIDIR con múltiples criterios

El tutorial muestra cómo buscar con múltiples criterios en Excel usando ÍNDICE y COINCIDIR y algunas otras formas.

Aunque Microsoft Excel proporciona funciones especiales para la búsqueda vertical y horizontal, los usuarios expertos normalmente las reemplazan con INDEX MATCH, que es superior a BUSCARV y BUSCARH en muchos sentidos. Entre otras cosas, puede buscar dos o más criterios en columnas y filas. Este tutorial explica la sintaxis y la mecánica interna con todo detalle para que pueda ajustar fácilmente la fórmula a sus necesidades particulares. Para que los ejemplos sean más fáciles de seguir, le invitamos a descargar nuestro libro de ejemplo.

Excel ÍNDICE COINCIDIR con múltiples criterios

Cuando trabaja con grandes bases de datos, a veces puede encontrarse en una situación en la que necesita encontrar algo pero no tiene un identificador único para la búsqueda. En este caso, la búsqueda con varias condiciones es la única solución.

Para buscar un valor basado en múltiples criterios en columnas separadas, use esta fórmula genérica:

{=ÍNDICE(rango_retorno, COINCIDIR(1, (criterio1=rango1) * (criterio2=rango2) * (…), 0))}

Dónde:

  • Return_range es el rango desde el cual devolver un valor.
  • Criterio1, criterio2,… son las condiciones a cumplir.
  • Range1, range2, … son los rangos en los que se deben probar los criterios correspondientes.

¡Nota IMPORTANTE! Esta es una fórmula matricial y debe completarse con Ctrl + Shift + Enter. Esto encerrará su fórmula entre {corchetes}, que es un signo visual de una fórmula de matriz en Excel. No intente escribir las llaves manualmente, ¡eso no funcionará!

La fórmula es una versión avanzada del icónico ÍNDICE COINCIDIR que devuelve una coincidencia basada en un solo criterio. Para evaluar criterios múltiples, usamos la operación de multiplicación que funciona como el operador AND en fórmulas de matriz. A continuación, encontrará un ejemplo de la vida real y la explicación detallada de la lógica.

ÍNDICE COINCIDIR con varios criterios – ejemplo de fórmula

Para este ejemplo, usaremos una tabla en el llamado formato de «archivo plano» con cada combinación de criterios por separado (región-mes-elemento en nuestro caso) en su propia fila. Nuestro objetivo es recuperar la cifra de ventas de un determinado artículo en una región y un mes específicos.

Con los datos fuente y criterios en las siguientes celdas:

  • Return_range (ventas) – D2: D13
  • Criterio1 (región objetivo) – G1
  • Criterio2 (mes objetivo) – G2
  • Criterios3 (elemento de destino) – G3
  • Range1 (regiones) – A2:A13
  • Rango2 (meses) – B2:B13
  • Range3 (artículos) – C2:C13

La fórmula toma la siguiente forma:

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Ingrese la fórmula, digamos en G4, complétela presionando Ctrl + Shift + Enter y obtendrá el siguiente resultado:
Fórmula Excel INDEX MATCH con múltiples criterios

Cómo funciona esta fórmula

La parte más complicada es la función MATCH, así que averigüémoslo primero:

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Como recordará, MATCH(lookup_value, lookup_array, [match_type]) busca el valor de búsqueda en la matriz de búsqueda y devuelve la posición relativa de ese valor en la matriz.

En nuestra fórmula, los argumentos son los siguientes:

  • valor_buscado: 1
  • matriz_de_búsqueda: (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13)
  • Tipo de coincidencia: 0

El primer argumento es muy claro: la función busca el número 1. El tercer argumento establecido en 0 significa una «coincidencia exacta», es decir, la fórmula devuelve el primer valor encontrado que es exactamente igual al valor de búsqueda.

La pregunta es: ¿por qué buscamos «1»? Para obtener la respuesta, echemos un vistazo más de cerca a la matriz de búsqueda donde comparamos cada criterio con el rango correspondiente: la región objetivo en G1 con todas las regiones (A2:A13), el mes objetivo en G2 con todos los meses (B2:B13 ) y el elemento de destino en G3 contra todos los elementos (C2:C13). Un resultado intermedio son 3 matrices de VERDADERO y FALSO donde VERDADERO representa valores que cumplen con la condición probada. Para visualizar esto, puede seleccionar las expresiones individuales en la fórmula y presionar la tecla F9 para ver cómo se evalúa cada expresión:
Cómo funciona INDEX MATCH con múltiples criterios

La operación de multiplicación transforma los valores VERDADERO y FALSO en 1 y 0, respectivamente:

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

Y debido a que multiplicar por 0 siempre da 0, la matriz resultante tiene 1 solo en las filas que cumplen con todos los criterios:

{0;0;1;0;0;0;0;0;0;0;0;0}

La matriz anterior va al argumento lookup_array de MATCH. Con lookup_value de 1, la función devuelve la posición relativa de la fila para la cual todos los criterios son VERDADEROS (fila 3 en nuestro caso). Si hay varios 1 en la matriz, se devuelve la posición del primero.

El número devuelto por COINCIDIR va directamente al argumento núm_fila del ÍNDICE(matriz, núm_fila, [column_num]) función:

=INDEX(D2:D13, 3)

Y arroja un resultado de $115, que es el tercer valor en la matriz D2:D13.

Fórmula INDEX MATCH sin matriz con múltiples criterios

La fórmula de matriz discutida en el ejemplo anterior funciona bien para usuarios experimentados. Pero si está creando una fórmula para otra persona y esa persona no conoce las funciones de matriz, es posible que la rompa sin darse cuenta. Por ejemplo, un usuario puede hacer clic en su fórmula para examinarla y luego presionar Enter en lugar de Ctrl + Shift + Enter. En tales casos, sería prudente evitar las matrices y usar una fórmula regular que sea más segura:

ÍNDICE(rango_retorno, COINCIDIR(1, ÍNDICE((criterio1=rango1) * (criterio2=rango2) * (..), 0, 1), 0))

Para nuestro conjunto de datos de muestra, la fórmula es la siguiente:

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Fórmula INDEX MATCH sin matriz con múltiples criterios

Cómo funciona esta fórmula

Como la función ÍNDICE puede procesar matrices de forma nativa, agregamos otro ÍNDICE para manejar la matriz de 1 y 0 que se crea al multiplicar dos o más matrices VERDADERO/FALSO. El segundo ÍNDICE está configurado con 0 argumento núm_fila para que la fórmula devuelva la matriz de columnas completa en lugar de un solo valor. Dado que es una matriz de una columna de todos modos, podemos proporcionar con seguridad 1 para column_num:

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

Esta matriz se pasa a la función MATCH:

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

COINCIDIR encuentra el número de fila para el cual todos los criterios son VERDADEROS (más precisamente, la posición relativa de esa fila en la matriz especificada) y pasa ese número al argumento número_fila del primer ÍNDICE:

=INDEX(D2:D13, 3)

COINCIDENCIA DE ÍNDICE con múltiples criterios en filas y columnas

Este ejemplo muestra cómo realizar una búsqueda probando dos o más criterios en filas y columnas. De hecho, es un caso más complejo de la llamada «búsqueda de matriz» o «búsqueda bidireccional» con más de una fila de encabezado.

Aquí está la fórmula genérica INDEX MATCH con múltiples criterios en filas y columnas:

{=ÍNDICE(matriz_tabla, COINCIDIR(vvalor_buscado, columna_buscada, 0), COINCIDIR(hvalor_buscado1 y hvalor_buscado2, fila_buscada1 y fila_buscada2, 0))}

Dónde:

Table_array: el mapa o el área de búsqueda, es decir, todos los valores de datos, excepto los encabezados de columnas y filas.

Vlookup_value: el valor que está buscando verticalmente en una columna.

Lookup_column: el rango de columna para buscar, generalmente los encabezados de fila.

Hlookup_value1, hlookup_value2, … – los valores que está buscando horizontalmente en filas.

Lookup_row1, lookup_row2, …: los rangos de fila para buscar, generalmente los encabezados de columna.

¡Nota IMPORTANTE! Para que la fórmula funcione correctamente, se debe ingresar como un fórmula matricial con Ctrl + Shift + Enter.

Es una variación de la clásica fórmula de búsqueda bidireccional que busca un valor en la intersección de una determinada fila y columna. La diferencia es que concatena varios valores y rangos hlookup para evaluar varios encabezados de columna. Para comprender mejor la lógica, considere el siguiente ejemplo.

Búsqueda de matriz con varios criterios: ejemplo de fórmula

En la tabla de muestra a continuación, buscaremos un valor basado en los encabezados de fila (Artículos) y los encabezados de 2 columnas (Regiones y Proveedores). Para hacer que la fórmula sea más fácil de construir, primero definamos todos los criterios y rangos:

  • Matriz_tabla – B3:E4
  • Vlookup_value (elemento de destino) – H1
  • Lookup_column (Encabezados de fila: elementos) – A3:A4
  • Hlookup_value1 (región de destino) – H2
  • Hlookup_value2 (proveedor objetivo) – H3
  • Lookup_row1 (Encabezados de columna 1: regiones) – B1:E1
  • Lookup_row2 (Encabezados de columna 2: proveedores) – B2:E2

Y ahora, proporcione los argumentos en la fórmula genérica explicada anteriormente, y obtendrá este resultado:

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

Recuerde completar la fórmula presionando el acceso directo Ctrl + Shift + Enter, y su búsqueda de matriz con múltiples criterios se realizará con éxito:
COINCIDENCIA DE ÍNDICE con múltiples criterios en filas y columnas

Cómo funciona esta fórmula

Como estamos buscando vertical y horizontalmente, necesitamos proporcionar los números de fila y columna para la función ÍNDICE (matriz, fila_num, columna_num).

Row_num es entregado por MATCH(H1, A3:A5, 0) que compara el elemento de destino (Apples) en H1 con los encabezados de fila en A3:A5. Esto da un resultado de 1 porque «Manzanas» es el primer elemento en el rango especificado.

Column_num se calcula concatenando 2 valores de búsqueda y 2 matrices de búsqueda: MATCH(H2&H3, B1:E1&B2:E2, 0))

El factor clave para el éxito es que los valores de búsqueda deben coincidir exactamente con los encabezados de las columnas y estar concatenados en el mismo orden. Para visualizar esto, seleccione los dos primeros argumentos en la fórmula COINCIDIR, presione F9 y verá lo que evalúa cada argumento:

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

Como «NorthVendor 2» es el segundo elemento de la matriz, la función devuelve 2.

En este punto, nuestra larga fórmula bidimensional INDEX MATCH se transforma en esta simple:

=INDEX(B3:E5, 1, 2)

Y devuelve un valor en la intersección de la primera fila y la segunda columna en el rango B3:E5, que es el valor en la celda C3.

Así es como buscar múltiples criterios en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!

Cuaderno de ejercicios para descargar

(archivo .xlsx)

  • BUSCARV con múltiples criterios: esto requiere agregar una columna de ayuda a la izquierda de su tabla y concatenar todos los criterios en esa columna. Esa no es una solución muy elegante, pero es posible que desee conocer todas las opciones disponibles.
  • XLOOKUP con múltiples criterios: esta reciente incorporación a la familia de funciones de búsqueda y referencia de Excel (actualmente disponible solo para suscriptores de Office 365) maneja arreglos por diseño. Es decir, funciona como una fórmula regular, ¡no como una fórmula matricial!
  • Busque múltiples criterios y devuelva múltiples resultados: combine 5 funciones diferentes en una sola fórmula para evaluar varios criterios y devuelva todas las coincidencias en una columna o fila.
  • Asistente para combinar tablas: forma visual de buscar V en Excel con uno o más criterios.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Experto Geek - Tu Guía en Tendencias Tecnológicas