compare two tables and find average

Hi,

I have two tables which consists of columns titled last name, first name, department and rank as shown below.

Screenshot 2022-11-10 185217.png

As you can see from table 1 and 2, they include names of people with their department and rank. Some names appear on both tables, some only once.

What I would like to do is use the first name, last name and department from my result table to locate their rank from both tables and then find the average.

If the person only exists in one table then take the rank from that table.

For example, Alan John appears in both tables, so his average rank would be (10+9)/2 = 9.5.

I have attached my workbook as well.

Thank you