Find column and row name based on search criteria

问题:

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

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.

---

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.

回答1:

Try the following...

G2:

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

G3, confirmed with CONTROL+SHIFT+ENTER:

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

回答2:

You could use something like this (entered as array formulas using Ctrl+Shift+Enter)