How to get the following result by using awk?

Question

There are two files I am going to extract the data:

file1.txt:

Type Number ID Element Email
Zed  21-2   9  Blade

file2.txt:

Name Order Email_Address
Ken  19    ken@gmail.com
Tom  21    tom@gmail.com
Ray  23    ray@gmail.com

How can I combine two files into a file with the following results:

Type Number ID Element Email
Zed  21-2   9  Blade   tom@gmail.com

Here is what I have tried before:

awk 'NR==1{print $0;next} NR==FNR{a[$2]=$1"\t"$3"\t"$4";next} {if($2 in a){print(a[$2]"\t"$3)}}' file1.txt file2.txt

I think I cannot get the results because of the if statement there have some problem. How can I get the wanted results?

Answer 1

$ awk 'BEGIN{print "Type Number ID Element Email"}NR==FNR{Arr[$2]=$NF;next}{split($2,b,"-");if(b[1] in Arr){print $0,Arr[b[1]]}}' file2.txt file1.txt
Type Number ID Element Email
Zed  21-2   9  Blade tom@gmail.com

Arr[$2]=$NF -> store the email address in Array with the index of column 2 split($2,b,"-") --> split the 2nd column value and use it for the lookup.

Answer 2

awk '
  NR == FNR {email[$2] = $3; next} 
  FNR == 1 {print; next}
  {
    for (order in email)
      if ($2 ~ "^" order "\\>") {
        print $0, email[order]
        break
      } 
  }
' file2.txt file1.txt | column -t

Type  Number  ID  Element  Email
Zed   21-2    9   Blade    tom@gmail.com

That regular expression looks for the order number at the start (^) of the Number field, with a word boundary (\>) so that order number "21" from file2 does not match number "211-1" from file1