I need help with an excel program im making?

Im running 2007 excel.





So, i have a table where you fill in numbers. its 14 by 14 with headers on both axises (sp). I have another table down below that takes the lowest three values of each column in the first table and puts them down in the second table, but according to what row they are in. ill try to explain:


..........Clm 1.....Clm 2......Clm 3


row 1:.....4............6.............8


row 2:.....5...........10............8


row 3:....15...........3.............6


row 4:.....7............7.............7


row 5:....10...........4.............4





What i need in the second table is a rule that will put the lowest three values of each column, but i need them to be labeled as what row they are in. So in column 1, it would be 4,5, and 7 as the lowest, but i would need it to show up as row 1, row 2, and row 4. And the same thing would work for column 2 and 3 and so on, all labeling with the Row they are in rather than their actual value.





hopefully this makes sense and i get someone that knows whats going on. A direct formula would be awesome if you can get one. or else just tell me how to write it. thanks|||like expletive_xom this took a little thought because of the possibility of ties



here is my best try so far



I've written the following as if the 14x14 set of numbers starts in B2 and extends to O15, if that is not a match you'll have to tweak it a bit...



the lowest value's row position in the first column would be



="row "%26amp;MATCH(SMALL(B2:B15,1),B2:B15,0)



the second lowest value would be on row



="row "%26amp;IF(SMALL(B2:B15, 1)=SMALL(B2:B15,2),MATCH(SMALL(B2:B15, 2), (OFFSET(B2,RIGHT(B17, LEN(B17)-4),0,14-RIGHT(B17, LEN(B17)-4),1)),0)+ RIGHT(B17,LEN(B17)-4), MATCH(SMALL(B2:B15, 2),B2:B15,0))



the third lowest value would be on row



="row "%26amp;IF(SMALL(B2:B15, 2)=SMALL(B2:B15,3),MATCH(SMALL(B2:B15, 3),(OFFSET(B2,RIGHT(B18, LEN(B18)-4),0,14-RIGHT(B18, LEN(B18)-4),1)),0)+RIGHT(B18, LEN(B18)-4),MATCH(SMALL(B2:B15, 3),B2:B15,0))



maybe there is a more elegant method, but at the moment the brute force version is all I have time for...



if you put these in a short stack in table 2 and fill right to get 14 columns it should be good to go



hope that helps