I want to use the VLOOKUP/MATCH/INDEX function to dynamically assign a
persons name to a record based on a zip code. I know how to do this but the
problem is that I can sometimes have in my table multiple people sharing a
zipcode and I was wondering if anyone has ever written a formula that would
cycle through the duplicates distributing out evenly amongst the people
sharing that zip.

Example:
37075 Smith
37078 Jones
38080 Brown
38080 Davis
38080 Daniels
38091 Owens

When the zip is 37075, I would want Smith assigned. When the zip is 38080,
I would want Brown assigned, then the next time I would want Davis assigned
to it, then the third time it would be Daniels. Then the assignments would
start over so that Brown would be assigned the fourth time that 38080 hits.

Does anyone know of a function within excel that would do this?

One crack at this ...

Illustrated in this sample:
http://www.flypicture.com/download/NjI2MQ==
Assigning names to zips in cyclic criteria.xls

Assuming the zip codes for: Smith, Jones, Owens
are listed in F1 down

while those for: Brown, Davis, Daniels
are listed in G1 down

With source zips running in A1 down

Place in B1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,F:F,0)),INDEX({" Smith";"Jones";"Owens"},MOD(COUNTIF(A$1:A1,A1)-1,3)+1),IF(ISNUMBER(MATCH(A1,G:G,0)),INDEX({"Brown ";"Davis";"Daniels"},MOD(COUNTIF(A$1:A1,A1)-1,3)+1),"Check Zipcode")))

Copy B1 down as far as required. Adapt the "3" within the MOD to suit the
actual number of names to be cycled*.
*The indexed part within the curly braces: {"Smith";"Jones";" ... }
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"investor9987" wrote:
I want to use the VLOOKUP/MATCH/INDEX function to dynamically assign a
persons name to a record based on a zip code. I know how to do this but the
problem is that I can sometimes have in my table multiple people sharing a
zipcode and I was wondering if anyone has ever written a formula that would
cycle through the duplicates distributing out evenly amongst the people
sharing that zip.

Example:
37075 Smith
37078 Jones
38080 Brown
38080 Davis
38080 Daniels
38091 Owens

When the zip is 37075, I would want Smith assigned. When the zip is 38080,
I would want Brown assigned, then the next time I would want Davis assigned
to it, then the third time it would be Daniels. Then the assignments would
start over so that Brown would be assigned the fourth time that 38080 hits.

Does anyone know of a function within excel that would do this?