Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 1 Time in 1 Post

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #3
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 1 Time in 1 Post
    Hi,
    I have attached an example of the cells and what the columns look like; I hope it helps.

    Greg
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #5
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 1 Time in 1 Post
    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #7
    Lounger
    Join Date
    Mar 2014
    Location
    Calgary, Alberta, Canada
    Posts
    25
    Thanks
    9
    Thanked 1 Time in 1 Post
    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
  •