Matrix 2-Way Lookup

Hi guys,

I am trying to put the coordinates of values placed inside a matrix in increasing order. I have put the matrix values into a column in increasing order I am now looking for a formula that could return the coodinates of each values. I have plugged in an array formula that does return values that look like the coordinates I am looking for but in a special date format.

I have played around with the formula but had no success in obtaining the raw coordinates from it.

Attached is the example with the table of what I am getting and what I would like to get from this array formula.

Any help would be greatly appreciated,

Thanks

Adam

Thanks pike,

I've arrived at something similar with the following formula (with which commas are not displayed in empty cells)

=TEXT(IF(COLUMNS($F2:F2)<=$P2,INDEX($E$2:$E$11,MATCH($Q2,INDEX($F$2:$O$11,0,SMALL(IF($F$2:$O$11=$Q2,COLUMN($F$1:$O$1)-COLUMN($F$1)+1),COLUMNS($F2:F2))),0)),""),"#")&TEXT(IF(COLUMNS($R2:R2)<=$P2,INDEX($F$1:$O$1,,SMALL(IF($F$2:$O$11=$Q2,COLUMN($F$1:$O$1)-COLUMN($F$1)+1),COLUMNS($R2:R2))),""),",#")

However, there is still a problem with either formulas: In cases where there are more than one occurence of the value, the formulas won't always return the appropriate second value's coordinates. It seems that if the vertical coordinate is not the same for the second value, it just returns the exact same coordinates.

I've tried filling the other half of the matrix which does allow to formula to return the expected coordinates but not without returning duplicates and reversed results.

Please find attached the same example with your formula plugged-in and my comments indicating where the coordinates returned are not as expected.

Very nice guys,

Would it be possible to have all the coordinates in a single column. I recognize that my inital formula was not design for that but I now realize such display would ease my subsequent calculations.

Also, the formula doesn't seem to be picking up well on the zero values as the coordinates returned are duplicated and are actually empty cells.

Please refer the attachment to see exactly what display would be preferable for my situation.

Thanks alot for everything!