Hi, I use a 3rd party excel plug in called Spheresoft that I use to calculate
distance in zip codes. My question is really a function of excel and not the
tool itself.
In short I have in Sheet1 a list of sites: address, city, state, zip etc.
I have a sheet called techs that list all the name and addresses of my techs.
Basically it looks at the zip code from sheet one and picks the one with the
lowest distance value. Works great.
However..
It would be great if i could have column c be the second closest tech.
Is there a way to have it exclude the tech from column b (which is the
closest), for the purpose of choosing the second closest.
My formula in column B is as follows.
=INDEX(techs!$H$2:$H$12,MATCH(zipcodesinlistwithin distance(Sheet1!I2,techs!$G$2:$G$12,$M$1),techs!$G $2:$G$12,0))
I2 contains the client zip code

If you have some kind of function that returns the actual distances as
numbers then you can look for the 2nd closest distance. for example:

10
22
25
12
31

10 would be the closest and 12 would be the 2nd closest. Finding 12 and the
corresponding tech would be relatively easy if this is how your program
works. But, you'd need to tell us where all the pertinent information is
located.

Or, perhaps your progam lists all the zip codes by closest proximity. If so,
then the function to find the 2nd closest zip would be the same as described
above.

12345
13452
17654
17777
18029

12345 would be the closest zip. 13452 would be the 2nd closest. It should
easy to find 13452 and the corresponding tech.

So, it all depends on what your add-in does!

--
Biff
Microsoft Excel MVP


"Craig860" wrote in message
...
Hi, I use a 3rd party excel plug in called Spheresoft that I use to
calculate
distance in zip codes. My question is really a function of excel and not
the
tool itself.
In short I have in Sheet1 a list of sites: address, city, state, zip etc.
I have a sheet called techs that list all the name and addresses of my
techs.
Basically it looks at the zip code from sheet one and picks the one with
the
lowest distance value. Works great.
However..
It would be great if i could have column c be the second closest tech.
Is there a way to have it exclude the tech from column b (which is the
closest), for the purpose of choosing the second closest.
My formula in column B is as follows.
=INDEX(techs!$H$2:$H$12,MATCH(zipcodesinlistwithin distance(Sheet1!I2,techs!$G$2:$G$12,$M$1),techs!$G $2:$G$12,0))
I2 contains the client zip code