Índice coincidente con varios criterios

Coincidencia de índices con filas y columnas de varios criterios

Todos usamos BUSCARV día tras día para obtener los datos y también somos conscientes del hecho de que BUSCARV puede obtener los datos de izquierda a derecha, por lo que el valor de búsqueda siempre debe estar en el lado izquierdo de las columnas de resultados. Sin embargo, tenemos varias alternativas que se pueden utilizar como alternativa a la función BUSCARV en Excel. Con tecnología avanzada, podemos usar esta fórmula INDICE + COINCIDIR para que coincida con múltiples criterios para filas y columnas. Así que este artículo especial le explicará en detalle esta técnica.

¿Cómo utilizar la fórmula INDEX + MATCH para coincidir con varios criterios?

Aquí explicamos cómo utilizar la fórmula índice + coincidencia para hacer coincidir varios criterios de filas y columnas con ejemplos.

Puede descargar esta plantilla de Excel de criterios múltiples de coincidencia de índice aquí - Plantilla de Excel de criterios múltiples de coincidencia de índice

Ejemplo # 1 - Fórmula INDICE + COINCIDIR

No la mayoría de las funciones de búsqueda de usuarios de Excel más allá de BUSCARV, las razones podrían ser muchas. De todos modos, hagamos una introducción simple a esta fórmula antes de pasar al nivel avanzado.

Por ejemplo, observe la siguiente estructura de datos en Excel.

Tenemos nombres de “Representantes de ventas” y sus respectivos valores de venta. Por otro lado, tenemos una lista desplegable de "Representante de venta" en la celda D2.

Según la selección que hagamos de la lista desplegable, el monto de las ventas debe aparecer en la celda E2.

El problema es que no podemos aplicar la fórmula VLOOKUP porque el valor de búsqueda "Representante de ventas" está a la derecha de la columna de resultados "Ventas", por lo que en estos casos podemos usar la fórmula de valor de búsqueda combinada INDICE + COINCIDIR.

INDICE busca el valor del número de fila mencionado en el rango A2: A11 y en este rango, necesitamos proporcionar de qué fila necesitamos que provenga el valor de ventas. Este valor de fila se basa en el nombre del "Representante de ventas" seleccionado en la lista desplegable en Excel, por lo que la función COINCIDIR busca el número de fila del "Representante de ventas" en el rango B2: B11 y devuelve el número de fila del valor coincidente .

Ejemplo # 2 - Criterios múltiples en la fórmula INDICE + COINCIDIR

Ahora tenemos una estructura de datos como la siguiente.

Tenemos valores de ventas mensuales de “Representante de ventas”. De esta tabla, necesitamos resultados dinámicos como en la celda A15, he creado una lista desplegable de "Representante de ventas" y en la celda B14 he creado una lista desplegable de "Mes".

Según la selección realizada en estas dos celdas, nuestra fórmula debe obtener los datos de la tabla anterior.

Por ejemplo, si elijo "Rep 8" y "Abr", entonces tiene que mostrar el valor de venta de "Rep 8" para el mes de "Abr".

Entonces, en estos casos, necesitamos hacer coincidir filas y columnas. Siga los pasos a continuación para aplicar la fórmula para que coincida con filas y columnas.

Paso 1: Abra la función INDICE en la celda B15.

Paso 2: El primer argumento de la función INDICE es “Matriz”, es decir, de qué rango de celdas necesitamos el resultado. Entonces, en este caso, necesitamos valores de ventas, así que elija el rango de celdas de B2 a G11.

Paso 3: Siguiente argumento de la función INDICE de qué fila del rango seleccionado necesitamos el resultado. En este caso, debemos llegar al número de fila del “Representante de ventas” según la selección realizada en la celda desplegable de la celda A15. Entonces, para obtener dinámicamente el número de fila en función de la selección, abra la función MATCH.

Paso 4: EL VALOR DE BÚSQUEDA de la función COINCIDIR es “Representante de ventas”, así que elija la celda A15 como referencia.

Paso 5: Lookup Array será el rango de nombres de "Representante de ventas" en la tabla principal. Así que elija un rango de A2 a A11.

Paso 6: El tipo de coincidencia de la función COINCIDIR será exacto, así que ingrese cero como valor del argumento.

Paso 7: El siguiente argumento de la función INDICE es "Número de columna", es decir, del rango de celdas seleccionado de la columna que necesitamos el resultado. Esto depende del mes que seleccionamos de la lista desplegable de la celda B14. Entonces, para obtener el número de columna, abra automáticamente otra función COINCIDIR.

Paso 8: Este valor de búsqueda de tiempo será el nombre del mes, así que seleccione la celda B14 como referencia.

Paso 9: La matriz de búsqueda será un rango de mes de celdas en la tabla principal, es decir, de B1 a G1.

Paso 10: El último argumento es el tipo de coincidencia, elija "Coincidencia exacta" como criterio. Cierre dos corchetes y presione la tecla Intro para obtener el resultado.

Como podemos ver arriba, hemos elegido "Rep 6" y "Abr" como el mes y nuestra fórmula ha devuelto el valor de ventas para el mes de "Abr" para "Rep 6".

Nota: la celda de color amarillo es la referencia para usted.

Cosas para recordar

  • Una combinación de INDICE + COINCIDIR puede ser más poderosa que la fórmula BUSCARV.
  • INDEX & MATCH puede hacer coincidir los encabezados de filas y columnas y devolver el resultado de la tabla del medio.
  • MATCH puede devolver el número de fila y el número de columna de los encabezados de tabla de filas y columnas.