ÍNDICE y COINCIDIR con múltiples matrices
Supongamos que tienes varias tablas con los mismos encabezados como se muestra a continuación, buscar valores que coincidan con los criterios dados en estas tablas puede ser una tarea difícil para ti. En este tutorial, hablaremos sobre cómo buscar un valor en múltiples matrices, rangos o grupos haciendo coincidir criterios específicos con las funciones ÍNDICE, COINCIDIR y ELEGIR.
¿Cómo buscar un valor en múltiples matrices?
Para conocer los líderes de diferentes grupos que pertenecen a diferentes departamentos, primero puedes usar la función ELEGIR para seleccionar la tabla desde la cual devolver el nombre del líder. La función COINCIDIR luego encontrará la posición del líder en la tabla a la que pertenece. Finalmente, la función ÍNDICE recuperará al líder basándose en la información de posición más la columna específica donde están listados los nombres de los líderes.
Sintaxis genérica
=ÍNDICE(ELEGIR(número_matriz,matriz1,matriz2,…),COINCIDIR(valor_búsqueda,rango_búsqueda,0),número_columna)
- número_matriz: El número que utiliza ELEGIR para indicar una matriz de la lista matriz1,matriz2,… desde la cual devolver el resultado.
- matriz1,matriz2,…: Las matrices desde las cuales se devuelve el resultado. Aquí se refiere a las tres tablas.
- valor_búsqueda: El valor que la fórmula combinada usa para encontrar la posición de su líder correspondiente. Aquí se refiere al grupo dado.
- rango_búsqueda: El rango de celdas donde está listado el valor_búsqueda. Aquí se refiere al rango de grupos. Nota: Puedes usar el rango de grupos de cualquier departamento ya que son todos iguales y solo necesitamos obtener el número de posición.
- número_columna: La columna que indicas desde la cual deseas recuperar datos.
Para conocer el líder del Grupo D que pertenece al Departamento A, copia o ingresa la siguiente fórmula en la celda G5, y presiona Enter para obtener el resultado:
=ÍNDICE(ELEGIR(1,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),COINCIDIR(F5,$B$5:$B$8,0),2)
√ Nota: Los signos de dólar ($) anteriores indican referencias absolutas, lo que significa que los rangos de nombre y clase en la fórmula no cambiarán cuando muevas o copies la fórmula a otras celdas. Después de ingresar la fórmula, arrastra el controlador de relleno hacia abajo para aplicar la fórmula a las celdas inferiores y luego cambia el número_matriz en consecuencia.
Explicación de la fórmula
=ÍNDICE(ELEGIR(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),COINCIDIR(F5,$B$5:$B$8,0),2)
- ELEGIR(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): La función ELEGIR devuelve la primera matriz de las tres matrices enumeradas en la fórmula. Por lo tanto, devolverá $B$5:$C$8, es decir, el rango de datos del Departamento A.
- COINCIDIR(F5,$B$5:$B$8,0): El tipo_de_coincidencia 0 obliga a la función COINCIDIR a devolver la posición de la primera coincidencia del Grupo D, el valor en la celda F5, en la matriz $B$5:$B$8, que es 4.
- ÍNDICE(ELEGIR(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),COINCIDIR(F5,$B$5:$B$8,0),22) = ÍNDICE($B$5:$C$8,4,22): La función ÍNDICE recupera el valor en la intersección de la cuarta fila y segunda columna del rango $B$5:$C$8, que es Emily.
Para evitar cambiar el número_matriz en la fórmula cada vez que la copias, puedes usar la columna auxiliar, la columna D. La fórmula sería así:
=ÍNDICE(ELEGIR(D5,$B$5:$C$8,,$B$11:$C$14,,$B$17:$C$20),COINCIDIR(F5,$B$5:$B$8,0),2)
√ Nota: Los números 1, 2, 3 en la columna auxiliar indican matriz1, matriz2, matriz3 dentro de la función ELEGIR.
Funciones relacionadas
La función ÍNDICE de Excel devuelve el valor mostrado basado en una posición dada de un rango o 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.
La función ELEGIR devuelve un valor de la lista de argumentos de valor basado en el número de índice dado. Por ejemplo, ELEGIR(3,”Manzana”,”Melocotón”,”Naranja”) devuelve Naranja, el número de índice es 3, y Naranja es el tercer valor después del número de índice en la función.
Fórmulas relacionadas
Buscar valores de otra hoja de trabajo o libro
Si sabes cómo usar la función BUSCARV para buscar valores en una hoja de trabajo, buscar valores de otra hoja de trabajo o libro no será un problema para ti.
BUSCARV con nombre de hoja dinámico
En muchos casos, es posible que necesites recopilar datos de varias hojas de trabajo para resumirlos. Con la combinación de la función BUSCARV y la función INDIRECTO, puedes crear una fórmula para buscar valores específicos en hojas de trabajo con un nombre de hoja dinámico.
Búsqueda de múltiples criterios con ÍNDICE y COINCIDIR
Al trabajar con una gran base de datos en una hoja de cálculo de Excel con varias columnas y filas de encabezados, siempre es complicado encontrar algo que cumpla con múltiples criterios. En este caso, puedes usar una fórmula matricial con las funciones ÍNDICE y COINCIDIR.
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.