INDEX MATCH值从矩阵 [英] INDEX MATCH value from matrix

问题描述

我使用索引匹配从一列中拉一个值，但是我想从矩阵中查找一个值吗？

我有一个列表的每个区域的一行（RowA2：A6）和每个错误类型的列（列A：F），然后按区域计算错误。然后我使用max函数从矩阵中获取最大值。然后我想查找与该值相关联的区域。但是这个价值可以从A3到F6。有没有办法可以通过这个矩阵进行索引匹配，而不只是一列？

解决方案

---

<sub>可视化识别的条件格式规则最大值和列和行标签均为：

红色适用于 $ C $ 3：$ G $ 6，UAFTDWCTF， = C3 = MAX（$ C $ 3：$ G $ 6）

橙色适用于 $ B $ 3：$ B $ 6，UAFTDWCTF， = COUNTIF （$ C3：$ G3，MAX（$ C $ 3：$ G $ 6））

橙色适用于 $ C $ 2：$ G $ 2 ，UAFTDWCTF， = COUNTIF（C $ 3：C $ 6，MAX（$ C $ 3：$ G $ 6））</sub>

---

¹ _{AGGREGATE功能与Excel 2010一起引入。它在早期版本中不可用。}

I am fine at using index match to pull a value from one column, but what I want to look up a value from a matrix?

I have a list of errors by area, so a row (RowA2:A6) for each area and a column (columns A:F) for each error type and then a count of errors by area. I then use a max function to get the maximum value from the matrix. I then want to look up which area is associated with this value. However the value can come from A3 to F6. Is there a way I can do index match over this matrix rather than just one column?

解决方案

The AGGREGATE¹ function can quickly locate the row or column containing the MAX value from a two-dimensioned matrix and pass that back to an INDEX of either the row or column header labels.

The formulas in E9:E10 are:

=INDEX(C2:G2, AGGREGATE(15, 6, COLUMN(A:E)/(C3:G6=MAX(C3:G6)), 1))
=INDEX(B3:B6, AGGREGATE(15, 6, ROW(1:4)/(C3:G6=MAX(C3:G6)), 1))

        

---

_{The conditional formatting rules that visually identify the maximum value and both column and row labels are:
    Red Applies to $C$3:$G$6, UAFTDWCTF, =C3=MAX($C$3:$G$6)
    Orange Applies to $B$3:$B$6, UAFTDWCTF, =COUNTIF($C3:$G3, MAX($C$3:$G$6))
    Orange Applies to $C$2:$G$2, UAFTDWCTF, =COUNTIF(C$3:C$6, MAX($C$3:$G$6))}

---

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

这篇关于INDEX MATCH值从矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！