I suggest that you add a helper column to the left of the data in Sheet 2 (you can hide the column if you don't want to see it). In that column put the full mailing name using the following formula in cell A2 and then copy to the other rows.
Sheet 2 will now look like this:
To make the data easier to refer to from Sheet 1, give the entire range of data (A1:J5 in the example) the name "Addresses" and give the headers (A1:J1 in the example) the name "AddrHdr"
In Sheet 1, you can use the
VLOOKUP function to find the row with the same Mailing Name and return the value from the column you want. In cell C2 on Sheet 1 enter the following formula and copy it to columns C through G of all the rows with data:
$A2&" "&$B2 returns the Mailing Name which
VLOOKUP finds in the first column of the range named "Addresses".
MATCH(C$1,AddrHdr,0) returns the position of the value in C1 ("Address 1") in the first row of Sheet 2 (which you named "AddrHdr") and
VLOOKUP returns the value in that column of the selected row.
Sheet 1 should now look like this:
If you don't want to see the "0"s in column D, you could format that column to display 0 as blank (set the Number Custom Format to "0;-1;". If you have other formulas that reference column D, have those formulas check the value for 0 and treat it the same as a blank.