Retrieving Table Headers

[Excel_Arate]

Hello everyone,

I am familiar with the index funtion, but in this case I wish to "reverse" it, i.e. given a reading of the table I would like to know the headeres of the corresponding columns and rows.
The following shows a correlation matrix, for example. If I were to enter a value 99.8%, I wish to receive the result, "Column A; Row F". I would be happy to calculate the column (A) and row (F) in separate cells, but I just can't find the right approach. I imagine, I have to "twist" the index or indirect function in some way...

A B C D E
A
B 87.4%
C 83.5% 75.9%
D 96.5% 82.7% 74.9%
E 87.6% 98.7% 77.5% 82.1%
F 99.8% 88.8% 84.6% 95.9% 89.1%


Can somebody help? For convenience/means of reference we can take the top left corner of the table to be cell A1 in Excel.

Best regards,
Excel_Arate

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

[CK76]

Assuming Column headers in range B1:F1, Row headers in A2:A7 range.
C10 holds 99.80%

Row:
=INDEX(A1:A7,SUMPRODUCT((B2:F7=C10)*ROW(B2:F7)))
=F

Column:
=INDEX(A1:F1,SUMPRODUCT((B2:F7=C10)*COLUMN(B2:F7)))
=A

See attached.

[Excel_Arate]

Hi CK76, Thank you so much! This is very useful and I could have surfed the web for another year without getting there. Cheers, Excel_Arate

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Excel_Arate]

Hi CK76 (or other interested user),

The solution provided works fine, but only if the section is not moved around (with or without inserting the "$" signs and also regardless whether I use the formula as an array or not). Your formula works, even without the arrays you have used.

Please see for yourself. Can you help me understand why this is so?

Best regards,
Excel_Arate

[CK76]

COLUMN(I2:M7) evaluates to...

{9,10,11,12,13}

Meaning it's Column Index# for I, J, K, L, M.

In INDEX function, you would want the Row/Column index to start from 1. So you need to offset the Column/Row range.

Note that I cheated a bit in the formula I used. Instead of giving offset to ROW/COLUMN, I just started the index range at A1. This effectively serves as offsetting index start from 2.

If the data range shifts. It's better to use below construct.

For Row:
=INDEX(A2:A7,SUMPRODUCT((B2:F7=C10)*(ROW(B2:F7)-ROW(B2)+1)))

For Column:
=INDEX(B1:F1,SUMPRODUCT((B2:F7=C10)*(COLUMN(B2:F7)-COLUMN(B2)+1)))

EDIT: Oh I forgot to mention. I highly recommend using Formula ribbon -> Evaluate formula to see how the formula evaluates.

[Excel_Arate]

Hi CK76,

For some reason I still can't get this to work. Meanwhile I will close the thread and do some more research on this. Thank you for looking into this.

Best regards,
Excel_Arate

[CK76]

In your sample workbook.

Paste in the formulas in post#7. Then copy paste into other areas as formula only. You should see reference ranges changing for each to adjust column/row start.

See attached.