So I have been looking and have failed. I am trying to divise a function that will find the smallest values in a range (distantce in miles) for a given employee and output the store number (column header) which is closest to that employee and inversely the employee that has the smallest distance to a given store. I need to do this for the 3 smallest values. I have attached a sample of the sheet in which i am working with. Thanks for looking

Il 26/09/2012 21:05, juanvaldez ha scritto:
So I have been looking and have failed. I am trying to divise a function
that will find the smallest values in a range (distantce in miles) for a
given employee and output the store number (column header) which is
closest to that employee and inversely the employee that has the
smallest distance to a given store. I need to do this for the 3 smallest
values. I have attached a sample of the sheet in which i am working
with. Thanks for looking


+-------------------------------------------------------------------+
|Filename: Test Book excel Banter.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=603|
+-------------------------------------------------------------------+




Put in L2
=INDEX($B$1:$J$1,MATCH(SMALL($B2:$J2,COLUMN(A1)),$ B2:$J2,0))
copy to L2:N12

Put in B16
=INDEX($A$2:$A$12,MATCH(SMALL(B$2:B$12,ROW(A1)),B$ 2:B$12,0))
copy to B16:J18

Hi,
E.

That works perfectly thanks so much