ÍNDICE y COINCIDIR a través de múltiples columnas

Para buscar un valor haciendo coincidir a través de múltiples columnas, una fórmula matricial basada en las funciones ÍNDICE y COINCIDIR que incorpora MMULT, TRANSPONER, y COLUMNA te hará el favor.

¿Cómo buscar un valor haciendo coincidir a través de múltiples columnas?

Para completar la clase correspondiente de cada estudiante como se muestra en la tabla anterior, donde la información está distribuida en múltiples columnas, puedes usar primero el truco de las funciones MMULT, TRANSPONER y COLUMNA para producir una matriz. Luego, la función COINCIDIR te dará la posición del valor buscado, que será entregado a ÍNDICE para recuperar el valor que estás buscando en la matriz.

Sintaxis genérica

=ÍNDICE(rango_devuelto,(COINCIDIR(1,MMULT(--(rango_búsqueda=valor_búsqueda),TRANSPONER(COLUMNA(rango_búsqueda)^0)),0)))

√ Nota: Esta es una fórmula matricial que requiere que la ingreses con Ctrl + Shift + Enter.

Para encontrar la clase de Jimmy, copia o ingresa la siguiente fórmula en la celda H5, y presiona Ctrl + Shift + Enter para obtener el resultado:

=ÍNDICE($B$5:$B$7,(COINCIDIR(1,MMULT(--($C$5:$E$7=G5),TRANSPONER(COLUMNA($C$5:$E$7)^0)),0)))

√ Nota: Los signos de dólar ($) anteriores indican referencias absolutas, lo que significa que los rangos de nombres y clases en la fórmula no cambiarán cuando muevas o copies la fórmula a otras celdas. Ten en cuenta que no debes agregar signos de dólar a la referencia de celda que representa el valor de búsqueda, ya que quieres que sea relativa cuando la copies 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.

Explicación de la fórmula

=ÍNDICE($B$5:$B$7,(COINCIDIR(1,MMULT(--($C$5:$E$7=G5),TRANSPONER(COLUMNA($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5): Este segmento verifica cada valor en el rango $C$5:$E$7 si son iguales al valor en la celda G5, y genera una matriz VERDADERO y FALSO como esta:
  {TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
  El doble negativo luego convertirá los VERDADEROS y FALSOS en 1s y 0s para generar una matriz como esta:
  {1,0,0;0,0,0;0,0,0}.
• COLUMNA($C$5:$E$7): La función COLUMNA devuelve los números de columna para el rango $C$5:$E$7 en una matriz como esta: {3,4,5}.
• TRANSPONER(COLUMNA($C$5:$E$7)^0) = TRANSPONER({3,4,5}^0): Después de elevar a la potencia 0, todos los números en la matriz {3,4,5} se convertirán en 1: {1,1,1}. La función TRANSPONER luego convierte la matriz de columnas en una matriz de filas como esta: {1;1;1}.
• MMULT(MMULT(--($C$5:$E$7=G5),,TRANSPONER(COLUMNA($C$5:$E$7)^0))) = MMULT(MMULT({1,0,0;0,0,0;0,0,0},,{1;1;1})): La función MMULT devuelve el producto matricial de las dos matrices como esta: {1;0;0}.
• COINCIDIR(1,MMULT(--($C$5:$E$7=G5),TRANSPONER(COLUMNA($C$5:$E$7)^0)),0) = COINCIDIR(1,{1;0;0},0): El tipo_coincidencia 0 obliga a la función COINCIDIR a devolver la posición de la primera coincidencia de 1 en la matriz {1;0;0}, que es 1.
• ÍNDICE($B$5:$B$7$B$5:$B$7,,,(COINCIDIR(1,MMULT(--($C$5:$E$7=G5),,,TRANSPONER(COLUMNA($C$5:$E$7)^0)),0))) = ÍNDICE($B$5:$B$7$B$5:$B$7,,,1): La función ÍNDICE devuelve el primer valor en el rango de clase $B$5:$B$7, que es A.

Para buscar fácilmente un valor haciendo coincidir a través de múltiples columnas, también puedes usar nuestro complemento profesional de Excel Kutools para Excel. Consulta las instrucciones aquí para realizar la tarea.

Funciones relacionadas

Fórmulas relacionadas