Match and replace value in 2 different places using awk

Hi,

I need help on replacing values in certain field in my file1.txt based on matched patterns in file2.txt using awk.

The blue color need to match with one of the data in field $2 in file2.txt. If match, BEGIN and FINISHED value in red will have a new value from field $3 and $4 accordingly.

file1.txt

ID   N_4736
NAME   N_4736
BEGIN  544
FINISHED  550
COM    Safe  

ID    N_32
NAME   N_32
BEGIN    872
FINISHED   1027
COM   Crash

Input file2.txt

test1     4754    120      160
test1     4736    500      600
test1     5487    1478     200      
test1     32      800      1000

The output should look like this:-

D   N_4736
NAME   N_4736
BEGIN  500
FINISHED   600
COM    Safe  

ID    N_32
NAME   N_32
BEGIN    800
FINISHED  1000
COM   Crash

i tried couple of codes that i found and do necessary modification but it's either gave me nothing or just display the same data again. I really don't know how to do this. :wall: appreciate your kind help on this. thanks

nawk -f red.awk file2.txt file1.txt

red.awk:

FNR==NR{b[$2]=$3;f[$2]=$4;next}
$1=="ID" {id=substr($2,index($2,"_")+1)}
id in b {$2=($1=="BEGIN")?b[id]:(($1=="FINISHED")?f[id]:$2)}
1

sed '1{x;p;x;}' f1| awk 'NR==FNR{a["N_"$2]=$3;b["N_"$2]=$4;} NR>FNR{RS=""; if ($2 in a) {$6=a[$2];$8=b[$2];for(i=1;i<=NF;i++){if(i%2==1)$i="\n"$i;}print $0}}' f2 - >output.txt

Hi Both,

i tried both methods and it seems that vgersh99 script works perfect to my issue.. Thanks so much guys!! :))