Results 1 to 7 of 7

Thread: Vlookup (2003)

  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vlookup (2003)

    Do you have to use VLookup?
    Is it not better to use a nested Sub Total from the Data Menu to get the totals!
    Regards,
    Rudi

  2. #2
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Vlookup (2003)

    (Edited by HansV to display data in table)

    How can I use the vlookup function to look for a part of a word.
    Example :

    <table border=1><td>Column A</td><td>Column B</td><td>Canada</td><td align=right>25</td><td>Canada - Toronto</td><td align=right>18</td><td>Canada - Montreal</td><td align=right>17</td><td>UK - London</td><td align=right>23</td></table>
    What vlookup formula can I use to return the totals for Canada only but to also include the two city breakouts ?

  3. #3
    Lounger
    Join Date
    Dec 2003
    Location
    London, England
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2003)

    No I don't have to use vlookup - anyone any ideas ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Vlookup (2003)

    The attached workbook contains two possibilities. In the first worksheet, country and city have been placed in separate columns, and subtotals by country have been added. In the second workbook, the location has been left "as is", but a column has been added to extract the country. Subtotals on this calculated column have been added.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    199
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Vlookup (2003)

    Hi,

    I would make it a two step operation:

    1. Use the LEFT function in another cell (C1) to separate the country from the city as such:
    =IF(ISERROR(FIND(" ",A1)),A1,LEFT(A1,FIND(" ",A1)-1))
    If you paste this into C1 it will provide the country name (one word, or no spaces) separate from the city. It looks for the first space and then extracts the characters to the left.

    2. You can then use SUMIF to get your totals:
    =SUMIF(C1:C4,"Canada",B1:B4)

    Good Luck!

    Peter Moran

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Vlookup (2003)

    What about the array formula: =SUM((LEFT(A1:A5,LEN(E1))=E1)*B1:B5) where E1 contains what you're looking for (e.g., "Canada" or "UK - London")

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Vlookup (2003)

    "To return the totals for Canada only but to also include the two city breakouts"

    How about

Posting Permissions

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