For (1), you could replace the MIN function in the formula with the SMALL function. For example:
=INDEX(Well!C:C,MATCH(SMALL(ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well!A $151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well! A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151))),2),ACOS(SIN(RADIANS(B7))*SIN(RADIANS(Well!A$2:Well! A$151))+COS(RADIANS(B7))*COS(RADIANS(Well!A$2:Well !A$151))*COS(RADIANS(C7-Well!B$2:Well!B$151))),0)+1,0)
SMALL ,1 gives the minimum
SMALL ,2 gives the second smallest,
and so on. To get the corresponding distances, you'd make the same changes to the distance formula.
As for (2), you'd probably need to create a lookup table for the distances and use a formula to retrieve the one you're after.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
|