Custom Search
Join the PC homebuilding revolution! Read the all-new, FREE 200-page online guide: How to Build Your Own PC!
NOTE: Using robot software to mass-download the site degrades the server and is prohibited. See here for more.
Find The PC Guide helpful? Please consider a donation to The PC Guide Tip Jar. Visa/MC/Paypal accepted.
Results 1 to 3 of 3

Thread: concenation

  1. #1
    Join Date
    Dec 2001
    Location
    maclean,australia
    Posts
    41

    concenation

    I am trying to use vlookup and the text concatenation(ampersand) in a formula, it will enter suburb,state, and pcode from the table on another worksheet'customers' in the same workbook to sheet 'invoice'. Can this be done.
    I must have the formula wrong.i think.
    =VLOOKUP("maitland&nsw&2320",customers!F8:H8,2,TRU E)

  2. #2
    Join Date
    Oct 2001
    Location
    N of the S of Ireland
    Posts
    20,502
    1. The table you are looking-up is a very small table with only one row on the customers spreadsheet!!

    2. The formula you have used is looking for the string maitland&nsw&2320 (or in fact the nearest match - since you have specifed the fourth parameter as true) in the F column (the leftmost one in the table); (specifically in cell F8 since there is only one row in your table).

    3. If you wanted to string-together (or concatenate) the elements maitland nsw and 2320, the correct syntax would be "maitland"&"nsw"&"2320"

    4. As long as the inverted commas have been set correctly, then the value of G8 (since the G column is column 2 in your table) should be returned as the result, since this is the only possible value in the one-rowed table.

    EDIT
    I think I now see what you are trying to do - that is if the three columns in customers are suburb, state and postcode.

    To use vlookup you would need a fourth column to the left of these three, containing some references such as customer names, etc.

    You would then need to concatenate three separate vlookups to return the three results as a string, viz:-

    =VLOOKUP("RefName",customers!E8:H20,2)&", "&VLOOKUP("RefName",customers!E8:H20,3)&", "&VLOOKUP("RefName",customers!E8:H20,4)&"."

    Each vlookup would look for the RefName in the e column and return the values for columns 2,3&4 or f,g&h or suburb,state&postcode.

    Also included in the formula is some punctuation so that for a value in column E called RefName, you should get the result:- maitland, nsw, 2320. assuming these are the values for RefName in that row of the table.
    Last edited by Paul Komski; 06-30-2003 at 07:13 PM.
    Take nice care of yourselves - Paul - ♪ -
    Help to start using BiNG. Some stuff about Boot CDs & Data Recovery Basics & Back-up using Knoppix.

  3. #3
    Join Date
    Dec 2001
    Location
    maclean,australia
    Posts
    41

    thks

    many thanks paul, that helped alot.
    I really was stuck on that.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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