Concatenate cell based on vlookup cell reference

I am trying to populate an address field based on a customer list on a different sheet of a workbook. Using Vlookup, I have gotten the customer name and address to pull properly using the customer ID as the lookup value, but I need to concantenate the 'City, State, Zip' fields into one cell. I can get it to pull the information okay if I change the cell reference in the formula each time, but I need it to pull automatically when you type in the vendor ID, just like the vlookup function. Any help would be greatly appreciated!

please post your sample data

2014 Purchase order FORM - TEMPLATE.xlsxThis is the bare bones of the workbook. Everything should pull based on cell H6. Right now, although the concatenate function works, it only pulls the data from row 2 instead of based on the vendor ID number.

Thanks for the reply, but that isn't quite what I need it to do. I want to pull the information from three cells on a different page, and put them all into one cell, and I only want to type in an ID number in a lookup reference cell. I think I need something that merges the concatenate function with the vlookup function - I'm just not sure how to go about it. You can see the workbook in my last post as an example.

Please Login or Register  to view this content.

That is exactly what I needed to do! Thank you both! Although I can't believe I didn't think of that before.......