Adding a new column in file1 that outputs a string in a reference file file2 that matches the value of another column in file1

Question

file1.txt (tab delimiter, with the second column containing a string with spaces):

A   Golden fog
B   Vibrant rainbow and sunny
C   Jumping, bold, and bright
D   Chilly/cold/brisk air

file2.txt (tab delimiter):

D01 Ti600   A
D02 Ti500   B
D16 Ti700   C
D20 Ti800   B

desired output for file3.txt (having a tab delimiter):

D01 Ti600   A   Golden fog
D02 Ti500   B   Vibrant rainbow and sunny
D16 Ti700   C   Jumping, bold, and bright
D20 Ti800   B   Vibrant rainbow and sunny

or at least this for file3.txt:

D01 Ti600   Golden fog
D02 Ti500   Vibrant rainbow and sunny
D16 Ti700   Jumping, bold, and bright
D20 Ti800   Vibrant rainbow and sunny

I have tried

awk 'NR==FNR{a[$1]=$2;next}{$3=a[$1];}1' file1.txt file2.txt > file3.txt

But I only get:

D01 Ti600 
D02 Ti500 
D16 Ti700 
D20 Ti800 

Which has a space deliminator instead of tabs, as well as a space after column 2, but no value in column 3.

Thanks so much for any help with getting the desired output.

Answer 1

Although you noted that the files are tab delimited, you did not actually make use of that. Also the common key A, B etc. is in the third field of file2.txt. So:

$ awk 'BEGIN{OFS=FS="\t"} NR==FNR{a[$1]=$2;next}{$4=a[$3];}1' file1.txt file2.txt
D01 Ti600   A   Golden fog
D02 Ti500   B   Vibrant rainbow and sunny
D16 Ti700   C   Jumping, bold, and bright
D20 Ti800   B   Vibrant rainbow and sunny

or (slightly shorter)

$ awk -F'\t' 'NR==FNR{a[$1]=$2;next}{print $0"\t"a[$3]}' file1.txt file2.txt
D01 Ti600   A   Golden fog
D02 Ti500   B   Vibrant rainbow and sunny
D16 Ti700   C   Jumping, bold, and bright
D20 Ti800   B   Vibrant rainbow and sunny