根据搜索条件查找列名和行名 [英] Find column and row name based on search criteria

问题描述

我有以下Excel电子表格:

I have the following Excel spreadsheet:

      A            B     C        D      E            F            G
1                 Q1     Q2      Q3     Q4   Search criteria:     60
2   Asset 1       15     85      90     70   Column name:         Q4
3   Asset 2       40     80      45     60   Row name:          Asset 2
4   Asset 3       30     50      55     10
5

在Cells A1:E4中，我的资产与季度Q1-Q4的资产不同.
我希望在Cell G2和G3中，根据放置在中的值来显示资产和季度.
在这种情况下，搜索条件为60，因此结果为列名Q4和行名Asset 2.

In Cells A1:E4 I have different assets with their performance from quarter Q1-Q4.
In Cell G2 and G3 I want that the assets and the quarter are displayed based on the value that is put in Cell G1.
In this case the search criteria is 60 so the result is column name Q4 and row name Asset 2.

使用 VLOOKUP 或 INDEX MATCH 我只能根据Cell G2和G3中的条件找到值60，但不能像我需要的那样反过来.

With VLOOKUP or INDEX & MATCH I would only be able to find the the value 60 based on the criterias in Cell G2 and G3 but not the other way around like I need it.

您对解决该问题的公式有任何想法吗?

Do you have any idea of a formula that could solve this issue?

注意:表中的所有值都是唯一的.

NOTE: All values in the table are unique.

推荐答案

尝试以下操作...

G2:

=INDEX(B1:E1,MATCH(G1,INDEX(B2:E4,MATCH(G3,A2:A4,0),0),0))

G3，通过CONTROL + SHIFT + ENTER确认:

G3, confirmed with CONTROL+SHIFT+ENTER:

=INDEX(A2:A4,SMALL(IF(B2:E4=G1,ROW(A2:A4)-ROW(A2)+1),1))

这篇关于根据搜索条件查找列名和行名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！