Lookup for vertical and horizontal corresponding values.

Hi all,

I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found.

eg: What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.

Please kindly refer to the attached sample for reference. Any help will be appreciated.

Thanks in advance.


Best Regards,

Eric

In O2:

Please Login or Register  to view this content.

In P2:

Please Login or Register  to view this content.

both formulas confirmed with CTRL+SHIFT+ENTER

and copied down.

Change formula in P2 to:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER...

As far as the last 2 entries of 27... I believe they are correct as the formulas produce them... There is an entry of 27 in Row 4, column 1.. which would come before the entry in row 5, column 10. There are 12 27's in your matrix, if you copy down another entry of 27 and copy down the respective formulas, you will get the next expected results of Row 5, Column 9.

Please see attached...where I created the next entry of 27 in red and show expected result.s