Q:

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

I have two files. (the two real files would be 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
This file might contain duplicate entries for any given conversion of raw score
e.g., raw score 8 and 9 both equal converted score of 50 for Science.
I'd like to create an output file consisting of 7 fields:
Name; Raw Reading Score; Converted Reading; Raw Math Score; Converted Math; Raw Science Score; Converted Science
So for Smith in my example below, the results of the scores 
3,7,4 should be:
3-5, 7-5, 4-15 (I've added spaced, dashes and commas for readabilty)
Sample File 1 (name and 3 raw scores)
Smith;3;7;4
Jones;8;2;9
Doe;1;9;4

Sample File 2 (raw and 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, then alternating 3 raw and 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 I think I'd like to read file 2 into an array and then read in file 1, using the array to look-up the converted scores and then output name and 3 sets of raw and converted scores.
Is this a do-able task for AWK, or should I look elsewhere?
Thanks,
Jim

A:

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

