INDEX MATCH值从矩阵

问题描述

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

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

解决方案

---

---

¹

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))

        

---

---

¹

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