Hi, Try: library(reshape2) m1 <- melt(LookupTable,id.vars="Str") m2 <- m1
res <- merge(MainDataFrame,m1,by.x=c("Str","index"),by.y=c("Str","variable")) res[order(res$Str),c(3:6,1:2,7)] #or library(plyr) colnames(m2)[-1] <- c("index","index_num") m2$index <- as.character(m2$index) join(MainDataFrame,m2,by=c("Str","index")) A.K. LookupTable <- read.table(header = TRUE, stringsAsFactors = FALSE, text="Str IND13 IND12 IND11 IND07 IND06 1 517 529 562 562 567 2 517 529 562 562 568 3 517 529 562 562 567 4 517 529 562 562 569 5 517 529 562 562 567 6 517 529 562 562 567 7 517 529 562 562 560 8 517 529 562 562 567 9 517 529 562 562 567 10 517 529 562 562 567") MainDataFrame <- read.table(header = TRUE, stringsAsFactors = FALSE, text="Pid YEAR MONTH Fips Str index 600250 2006 7 6037 1 IND06 600250 2006 7 6037 2 IND06 600250 2006 7 6037 3 IND06 600250 2006 7 6037 4 IND06 600250 2006 7 6037 5 IND06 600353 2007 9 48097 6 IND07 600772 2006 2 6039 7 IND06 600947 2007 1 13207 7 IND07 601055 2007 9 13315 8 IND07 601103 2006 5 21093 10 IND06") MainDataFrame_New <- ?? #What is the best way to add a new column "index_num" to MainDataFrame that is populated with the #number corresponding to 'Str' and 'index' in LookupTable: # Pid YEAR MONTH Fips Str index index_num # 600250 2006 7 6037 1 IND06 567 # 600250 2006 7 6037 2 IND06 568 # 600250 2006 7 6037 3 IND06 567 # 600250 2006 7 6037 4 IND06 569 # 600250 2006 7 6037 5 IND06 567 # 600353 2007 9 48097 6 IND07 562 # 600772 2006 2 6039 7 IND06 560 # 600947 2007 1 13207 7 IND07 562 # 601055 2007 9 13315 8 IND07 562 # 601103 2006 5 21093 10 IND06 567 ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.