Thanks. That solved the problem perfectly. I tested with
three boxes of ten compartments each, using a sequence of
numbers 123, 124, etc. sorted in random order for column
A, and the same sequence sorted in another order for
column B. This was to simulate the random order of the
parts at each end of the process. I found that the part
of the formula MATCH(B1,A$1:A$30) needed to be MATCH
(B1,A$1:A$30,0) since the lookup array could be in any
order. At least, that's why I think it worked based on my
study of the MATCH worksheet function.
I was able to fairly easily comprehend the MOD, MATCH, and
FLOOR worksheet functions individually, but it took me
quite a while to sort out how they work together to
produce the desired result. I really appreciate the time
you took to develop and test the formula. I did not know
that Excel even had those capabilities, so I doubt I would
have been able to devise such an effective solution on my
own. Thanks again.
-----Original Message-----
Put this formula in column C to get Box and Compartment
number. This was tested on 8 boxes of 2 compartments
each, but should work with more boxes of ten
compartments. Just replace each '2' with a '10'.

="Box "&IF(MOD(MATCH(B1,A$1:A$8),2)=0,MATCH
(B1,A$1:A$8)/2,FLOOR(MATCH(B1,A$1:A$8)/2,1)+1)&";
Compartment "&IF(MOD(MATCH(B1,A$1:A$8),2)=0,2,MOD(MATCH
(B1,A$1:A$8),2))
.