Finding the row and column header for a value in a matrix

LS.

I am working on a lage matrix with row headers in column A and column headers in row 1.
All data in the sheet is numerical data and unique (loads of numbers behind the comma).

using the LARGE formula I sorted all data from high to low but now I want for each data item to see the associated row and column labels.
I tried using the RLrow and RLcol formula's unfortunately unsuccesful. #NAME error message.

Hope you can help me set this up

Originally Posted by Søren Larsen

=VLOOKUP()/HLOOKUP() or =INDEX(MATCH()) should do the trick. What are the dimensions of your data-table?

The dimensions are: A1:QI450
Using V/HLOOKUP will not give me the data labels in the A column or row 1 as these formula's only seek for values to the right/down from the lookup value. I need to find the labels in the first column and first row of the dataset.

Regards,

Johan

Originally Posted by Søren Larsen

EDIT: Do you want to return row/column headers in a table, or are you looking up one value at a time?

When you sort the data, do sort both on columns and rows, or only on one of them?

Thanks Søren,

imagine the following partial sheet with unique numbers in it:

apple pear banana
red 32 67 99
green 55 94 12
blue 43 66 18

From the LARGE funktion LARGE(B2:D4,1) I get the largest value as 99 in cell D2.

Now my problem is I would like a function to search for 99 in the spreadsheet en return me the column and row names for that value, so in the case of 99 red as row and banana as colum.

Regards,

Johan

Additional: The data is not sorted and refresed (potentially changes) evertime the sheet is opened.
Looking up 1 row/column header at a time is fine.

Hi Johan,

I had a similar query a few days back, I think the same might work for you.

http://www.excelforum.com/excel-work...49#post2793949

Please note that you may have to modify it for you to get the row and column headers

Regards,

Veejar