INDICE y COINCIDIR en varias columnas

Para buscar un valor coincidiendo en varias columnas, una fórmula matricial basada en las funciones INDICE y COINCIDIR, que incorpora las funciones MMULT, TRANSPONER y COLUMNA, le será de gran ayuda.

¿Cómo buscar un valor haciendo coincidir varias columnas?

Para rellenar la clase correspondiente de cada alumno, como muestra la tabla anterior —donde la información aparece distribuida en varias columnas—, primero puede usar una combinación de las funciones MMULT, TRANSPONER y COLUMNA para generar una matriz. A continuación, la función COINCIDIR le proporcionará la posición del valor buscado, que luego se pasará a INDICE para recuperar el valor deseado en dicha matriz.

Sintaxis genérica

=INDEX()return_range,(MATCH(1,MMULT(--())))lookup_array=lookup_value),TRANSPOSE(COLUMN()lookup_array)^0)),0)))

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

Para encontrar la clase de Jimmy, copie o introduzca la siguiente fórmula en la celda H5 y pulse Ctrl+Mayús+Intropara obtener el resultado:

=INDICE()$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 nombre y clase en la fórmula no cambiarán al moverla o copiarla a otras celdas. Tenga en cuenta que no debe añadir signos de dólar a la referencia de celda que representa el valor de búsqueda, ya que desea que sea relativa al copiarla a otras celdas. Después de introducir la fórmula, arrastre el controlador de relleno hacia abajo para aplicar la fórmula a las celdas inferiores.

Explicación de la fórmula

=INDEX()$B$5:$B$7,(MATCH(1,))MMULT()--($C$5:$E$7=G5),TRANSPOSE()COLUMN($C$5:$E$7)^0)),0)))

• --($C$5:$E$7=G5): Este segmento comprueba si cada valor del rango $C$5:$E$7 es igual al valor de la celda G5 y genera una matriz de VERDADERO y FALSO como esta:
  {VERDADERO,FALSO,FALSO;FALSO,FALSO,FALSO;FALSO,FALSO,FALSO}.
  El doble signo negativo convierte después los valores VERDADERO y FALSO en 1 y 0, generando 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 del 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):Al elevarlos a la potencia 0, todos los números de la matriz {3,4,5} se convierten en 1: {1,1,1}. A continuación, la función TRANSPONER transforma esa matriz de columna en una matriz de fila como esta:{1;1;1}.
• MMULT()--($C$5:$E$7=G5),TRANSPONER()COLUMNA($C$5:$E$7)^0))=MMULT(){1,0,0;0,0,0;0,0,0},{1;1;1}):La función MMULT devuelve el producto matricial de ambas matrices, que en este caso es:{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 argumento tipo_de_coincidencia con valor 0 obliga a la función COINCIDIR a devolver la posición de la primera coincidencia del valor 1 en la matriz {1;0;0}, que es 1.
• INDICE()$B$5:$B$7,(COINCIDIR(1,))MMULT()--($C$5:$E$7=G5),TRANSPONER()COLUMNA($C$5:$E$7)^0)),0))) = INDICE($B$5:$B$7La función INDICE devuelve el 1.º valor del rango de clases $B$5:$B$7, que es A.

Para buscar fácilmente un valor coincidente en varias columnas, también puede utilizar nuestro complemento profesional para Excel Kutools para Excel.Consulte aquí las instrucciones para llevar a cabo esta tarea.

Funciones relacionadas

Fórmulas relacionadas