I have a workbook with 2 sheets:

  • Sheet 2 has a list of names and addresses

  • Sheet 1 only has a portion of these names but no addresses.

I need a formula to take the addresses from Sheet 2 and add them to the matching names on Sheet 1.

Currently the names are split into first name (column A) and last name (column B) in both sheets. The addresses I need copied over are in columns E-I in Sheet 2 and need to go to columns C-G in Sheet 1.

I have tried to do this myself based on other suggestions, however I keep coming up short. Any help is greatly appreciated!

ex. Sheet 2

Example of what Sheet 2 looks like

ex. Sheet 1

Example of what sheet 1 looks like

  • Please Edit your post and for better understanding share some sample data with us! – Rajesh S Nov 18 '19 at 19:15

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.

=$B2&" "&$C2

Sheet 2 will now look like this:

enter image description here

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:

=VLOOKUP($A2&" "&$B2,Addresses,MATCH(C$1,AddrHdr,0),FALSE)

$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:

enter image description here

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.

  • That worked perfectly. Thank you! – Dee Nov 19 '19 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.