Use AWK to look-up 3 values from 1 file using 3 values from a second file -

i have 2 files. (the 2 real files 50-100 lines long).

file 1 has records consisting of 4 fields. name; raw reading score; raw math score; raw science score

file 2 has records (a look-up table) consisting of 4 fields raw score; converted reading; converted math; converted science file might contain duplicate entries given conversion of raw score e.g., raw score 8 , 9 both equal converted score of 50 science.

i'd create output file consisting of 7 fields: name; raw reading score; converted reading; raw math score; converted math; raw science score; converted science

so smith in example below, results of scores 3,7,4 should be: 3-5, 7-5, 4-15 (i've added spaced, dashes , commas readabilty)

sample file 1 (name , 3 raw scores)

smith;3;7;4 jones;8;2;9 doe;1;9;4 

sample file 2 (raw , 3 converted scores)

1;1;1;1 2;3;2;5 3;5;2;10 4;6;3;15 5;8;4;22 6;11;5;35 7;15;5;43 8;18;6;50 9;20;7;50 

desired output file (name, alternating 3 raw , 3 converted scores)

smith;3;5;7;5;4;15 jones;8;18;2;2;9;50 doe;1;1;9;7;4;15 

so think i'd read file 2 array , read in file 1, using array look-up converted scores , output name , 3 sets of raw , converted scores.

is do-able task awk, or should elsewhere?

thanks,

jim

this should work:

awk -f';' -v ofs=";" 'nr==fnr{a[$1]=$0;next} { split(a[$2],b) split(a[$3],c) split(a[$4],d) print $1,$2,b[2],$3,c[3],$4,d[4]}' file2 file1