Hi,

I have a huge matrix (500*500) with value differences of 500 companies between eachother.
I now want to find the 10 companies with the closests/smallest value difference for each company.

I have therefor made and formula {=SMALL(IF(C3:SH3>0;C3:SH3);{1,2,3,4,5,6,7,8,9,10})} to find the 10 smallest value differences for company A (row C3:SH3), but at the same time ignore blanks or zeroes.

However this formula wont transpose for the following reasons:

1) The I transpose it to cower 10x500 rows the cell reference to C3:SH3 wont change to C4:SH4 and so on... Why?
2) The k-value (to find the 10 closest values) changes from {1,2,3,4,5,6,7,8,9,10} to {1,234567891}. Why?

Can someone help?

Thansk in advance!

Fred