View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Marcotte Anderson Marcotte Anderson is offline
external usenet poster
 
Posts: 1
Default 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))