Code to search for address via Name

Hey guys! Love the help you guys have been able to give me so far troubleshooting my code, great site overall.

I have another problem I'm trying to solve, where I have a big list of customer names and addresses (addresses adjacent to names in columns). I have created a dropdown list (Data validation) with all the names, and I would like to be able to select the one I want, and to return the address in two lines below it, the first for street, the second for City, State, and Zip.
123456.png
My Name box is named "NAME_SEARCH" , first address line "ADDRESS_RET_L1" and second address line "ADDRESS_RET_L2"

What would be the easiest way for me to get this done?

Thanks!

Thank you!
One more question, is there any chance for me to do this, while keeping the list in a separate workbook? Right now the addresses are in a separate sheet, but could I have them in a different excel file, as to keep my main one from taking up so much space?

Ok, so I have "=VLOOKUP(NAME_SEARCH,[Book1.xlsx]CUST_ADDRESS_2!$C$2,2,FALSE)" in there, for ADDRESS_RET_L1, and it's giving me a #value! error, any idea why this might be? The second line is giving me the same error, I have "=VLOOKUP(NAME_SEARCH,[Book1.xlsx]CUST_ADDRESS_2!$D$2+[Book1.xlsx]CUST_ADDRESS_2!$E$2+[Book1.xlsx]CUST_ADDRESS_2!$F$2,FALSE)" in that one, since I want to string together a few different columns. I don't see a reason why these don't work, besides mabye the "+"s in the second one, should those be (" & ")?
"