Query from file using awk

I have a file of 6 columns:

$cat data1.txt

123711184642,02,3583090366663629,639f02,292,14292
123715942138,01,3538710295145500,639f02,45014,50755
123711616258,02,3548370476972758,639f02,72,22322
123726139528,02,3532810125937435,639f02,8,3562

I have a second file(data2) of 3 columns

64340,5616,abc  
64341,5616,def  
64342,5616,ghi  
64344,5616,hjk  
64345,5616,lmn开发者_开发问答  
64346,5616,opq  

I need to compare the last column of data1 with 1st or 2nd column of data2.If there's a match, the output file data3 will have 3rd field of data2.E.g:

123711184642,02,3583090366663629,639f02,292,14292,abc
123715942138,01,3538710295145500,639f02,45014,50755,def
123711616258,02,3548370476972758,639f02,72,22322,ghi
123726139528,02,3532810125937435,639f02,8,3562,lmn

Thanks.

Bernie

Assuming that the second file is a small lookup file and the first file is a large data file:

#!/usr/bin/awk -f
BEGIN {
    FS = OFS = ","
}

NR == FNR {
    lookup1[$1] = lookup2[$2] = $3
    next
}

{
    if (lookup1[$NF]) {
        $(NF+1) = lookup1[$NF]
        print
    }
    else if (lookup2[$NF]) {
        $(NF+1) = lookup2[$NF]
        print
    }
}

To run it:

$ ./script.awk data2.txt data1.txt

Using this as data2.txt:

14292,333,zzz
555,777,nnn
222,22322,xxx
111,444,yyy

and the data1.txt from your question, the result:

123711184642,02,3583090366663629,639f02,292,14292,zzz
123711616258,02,3548370476972758,639f02,72,22322,xxx

join the two tables on the relevant fields then print the last field of the result using awk

To find matches on the first field of file 2:

join -1 76 -2 1 -t, data1.txt data2.txt | awk `BEGIN{FS=","}{print($NF)}`

Then match on the second field of file 2 and concatenate the two sets of results:

join -1 76 -2 2 -t, data1.txt data2.txt | awk `BEGIN{FS=","}{print($NF)}`

Chapter 4 of The AWK Programming Language covers this kind of stuff really well, including writing a version of join in AWK if it's not available on your system.