valores - match excel

Búsqueda de dos columnas en una matriz de tabla usando INDEX y MATCH

Question

Me gustaría que Excel muestre el valor de la matriz de tabla que tiene dos celdas coincidentes com_cd y div_cd usando INDEX y MATCH .

He intentado la siguiente fórmula pero no funcionó.

=INDEX(K9:K53,MATCH(K3,I9:I53,0),MATCH(K4,J9:J53,0)) 

Aquí hay una captura de pantalla de la hoja de Excel con el resultado deseado dado de acuerdo con com_cd y div_cd

Answer 1

Pruebe una fórmula de matriz (CTRL + SHIFT + ENTER) lugar de Enter .

=INDEX(K9:K53,MATCH(K3&K4,I9:I53&J9:J53,0),1)

No probado pero debería funcionar.

Lo editará más adelante explicando nuestra fórmula y la razón por la cual su fórmula no funciona.

Answer 2

Su parámetro column_num en la función INDEX no puede proporcionar un criterio de fila secundario. Necesita una forma de garantizar una coincidencia de dos columnas en el parámetro row_num y dejar column_num en blanco o como 1 (solo hay una columna en K9:K53 ).

La fórmula estándar para K5 debería ser,

=index(K9:K53, aggregate(15, 6, row(1:45)/((i9:i53=k3)*(j9:j53=k4)), 1))

... o

=index(K9:K53, min(index(row(1:45)+((i9:i53<>k3)+(j9:j53<>k4))*1e99, , )))

El rango de celdas K9: K53 tiene un total de 45 filas. La posición dentro de K9: K53 estará dentro de ROW(1:45) . ¡La primera fórmula fuerza cualquier fila que no coincida en un #DIV/0! estado de error y la función AGREGAR¹ utiliza la opción 6 para ignorar errores al recuperar la entrada válida más pequeña con la subfunción PEQUEÑA (por ejemplo, 15 ). La segunda fórmula realiza la misma acción agregando 1E + 99 (un 1 seguido de 99 ceros) a cualquier fila no coincidente y tomando la fila coincidente más pequeña con la función MIN .

¹ _{La función AGREGAR se introdujo con Excel 2010. No está disponible en versiones anteriores.}