# Thread: LookUp Value and Concatenate

1. ## LookUp Value and Concatenate

Hello,
I am trying to set up an invoice template which ties into a client database. The client list has the City, Province, and Postal Code each in their own columns/cells and I would like to combine this into one cell on the invoice template. Can anyone please help with the LookUp Value and Concatenate code/formula? (p.s. I am not knowledgeable with code).
Thanks!

2. The simple formula would be (Change the descriptions to actual cell references):
CityCell &", " & ProvinceCell & " " & PostalCodeCell

If you want to look them up in some way, you will have to provide more details. Can you attach a sample workbook?

Steve

3. Hi,
I have attached an example of the cells and what the columns look like; I hope it helps.

Greg

4. Not sure exactly what you are after, but if you add data validation to A2 you can select from the list in I2:I6 (adjust as needed)
Capture.PNG

Then in B2 add the formula:
=VLOOKUP(\$A\$2,\$I\$2:\$M\$6,2,0)

and in C2 the formula:
=VLOOKUP(\$A\$2,\$I\$2:\$M\$6,3,0)&", "&VLOOKUP(\$A\$2,\$I\$2:\$M\$6,4,0)&", "&VLOOKUP(\$A\$2,\$I\$2:\$M\$6,5,0)

And the address, city/province/postal code will be displayed for whatever customer you select.

Steve

5. Thanks Steve, I will give that a try today ... for the life of me, I couldn't figure out the "&" part of the formulas.

Greg

6. The Ampersand (&) is the text equivalent of the plus (+) in adding...

You could also use the CONCATENATE function (akin to the SUM function):
=Concatenate(VLOOKUP(\$A\$2,\$I\$2:\$M\$6,3,0), ", ", VLOOKUP(\$A\$2,\$I\$2:\$M\$6,4,0), ", ", VLOOKUP(\$A\$2,\$I\$2:\$M\$6,5,0))

But I find the ampersand easier (and faster) to use

Steve

7. Thanks, I tried the + and the like yesterday and wouldn't work but I see that I have to have it inside of the quotes.
I will try this in about an hour when I have a moment to breath LOL

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•