Matching cell in one column to value in another
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))
|