Use awk to compare the differences between two files when a column matches

I have two txt files that I want to compare. I want to calculate the BudgetSpent between the two files if and only if the first column (FirstName) and second column (LastName) in the first txt file matches the second file's first and second column.

So basically if file1.txt's Firstname == file2.txt's Firstname && file1.txt's LastName== file2.txt's LastName, I want it to generate a third file whereby it generates all the columns that matched, and one extra column which would be the BudgetSpentDifferences.

file1.txt

FirstName LastName Age BudgetSpent
Taylor Swift 23 1000
Joe Smith 30 5000

file2.txt

FirstName LastName Age BudgetSpent
Joe Smith 30 5200
Taylor Swift 23 800
Joe Johnson 22 100

This is how I want the third file generated to be:
file3.txt

FirstName LastName Age BudgetSpentDifferences
Taylor Swift 23 200
Joe Smith 30 -200

I am really confused as I am quite new to awk. Please help me out I've been scrambling my head thinking about how to generate this. Not sure if there's an if condition that can input two files in awk.

Thanks,

awk 'NR==FNR && NR>1 { a[$1 " " $2] = $4; next}
NR!=FNR && FNR==1 { $4 = "BudgetSpent"; print }
NR!=FNR && FNR>1 && ($1 " " $2) in a { $4 -=  a[$1 " " $2]; print}
' file1.txt file2.txt

prints

FirstName LastName Age BudgetSpent
Joe Smith 30 200
Taylor Swift 23 -200

Explanation:

• NR == FNR is true for the first file
• NR > 1 is true for the second and later lines total
• FNR == 1 is true for the first line of every file
• a[$1 " " $2] = $4 creates an entry in an associative array a with the combination FirstName LastName (separated by a single space) as key and BudgetSpent as value
• ($1 " " $2) in a is true if the combination FirstName LastName is a valid key in a
• $4 -= a[$1 " " $2] subtracts the value retrieved from a with key FirstName LastName from the value of field 4 (BudgetSpent)

Note that the script will not work if FirstName or LastName contains spaces. Apart from using some other character (e.g. TAB) as field separator, the array key might be ambiguous in this case.

There are nearly always different / simpler / shorter ways to solve a problem.

Citing jhnc's comment:

> NR>1 and NR!=FNR conditions are unnecessary.

If the condition NR>1 is omitted, the array a will also contain an entry based on the heading which should not be a problem. As the first condition then covers all lines of the first file and the action contains next, the second and third line of the script will be executed only for the second file (and later), so the condition NR!=FNR can be removed.

> You can reduce typing by assigning composite key to a variable: {u=$1" "$2} NR==FNR{a[u]=$4;next} u in a{ $4 = FNR==1?"Diff":$4-a[u]; print }

For GNU awk use a multi-demensional array for the name index:

awk 'NR==1{print $0"Differences"; next}
     NR==FNR{a[$1][$2]=$4; next}
     $1 in a && $2 in a[$1]{$4-=a[$1][$2]; print}' file1 file2

For POSIX awk, use spaces to index "FirstName LastName":

awk 'NR==1{print $0"Differences";next}
     NR==FNR{a[$1" "$2]=$4;next}
     $1" "$2 in a{$4-=a[$1 " " $2]; print}' file1 file2

For POSIX awk: If input names may contain spaces (but no tabs), use -F'\t' to set input field separator to tab and change " " to "\t":

awk -F'\t' 'NR==1{print $0"Differences";next}
   NR==FNR{a[$1"\t"$2]=$4;next}
   $1"\t"$2 in a{$4-=a[$1"\t"$2]; print}' file1 file2

Result:

FirstName LastName Age BudgetSpentDifferences
Joe Smith 30 200
Taylor Swift 23 -200

• Line 1: Print "FirstName LastName Age BudgetSpentDifferences".
• Line 2: Create a two-dimensional index in the form array[FirstName][LastName]=BudgetSpent from file1.
• Line 3: In case of an index match in file2, subtract the Budgets and print the results.

to build upon @steffen's, solution, here's one for all awks :

awk 'BEGIN { ____ = (_+= __ = _^= SUBSEP = FS )^_ 
     } __ == NR && sub("$", "Differences")  ||
      FNR == NR ? (___[$__, $_] = $____)<"" : \
     ($__, $_) in ___ && ($____ -= ___[$__, $_])^!_' f1 f2