Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Function other than VLookup

    Hi, I attempted to use the V-Lookup function to identify the funds of Tables 1 and 2 with the same name. I attempted to place the Funds of Table 2 into the columns of Table 1. Unfortunately, V-Lookup only pulls the data of the first Match it sees. I have many funds that need to be matched to single occurrences of names.

    Any other functions available?






    Table 1
    Name Fund Amount
    BHAGAT 000001 2,316.38

    Table 2
    Source Name FUND Balance
    Fund Summary Bhagat 554102 25000
    Fund Summary Bhagat 554714 251.65
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    I am not sure what you are trying to do.

    You only have one cell adjacent to the names in Column F of table 2. So how/where do you want to represent the other Funds ? As always, an example of how you'd like it to look would be most helpful.

    PS If you were seeking to total the funds held by each person, that would be easy !

  3. #3
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Hi, Martin, thanks for getting back to me. Below is what I'd like to see: the fund numbers from table 2 matched to the appropriate names from Table 1.

    My project is to transfer dollars from the 00001 (general) fund numbers and allocate to the various funds in table 2.

    Names Fund Amount Fund assigned per Name
    BHAGAT 000001 2,316.38 554102 554714
    BONNEY 000001 3,666.67 554710 556627 556352
    CARBERRY 000001 5,991.34 549666 559417 554266 552095

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    OK, working on it.
    Meantime, where did the 2316.38 next to BHAGAT come from - it isn't the sum of the funds against BHAGAT in Table 2.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    An example file of how you can achieve your result is attached. Its is in Excel 2003.

    If you look at the code I have written, you will see that I had to make all your names UPPERCASE as you have a mixture in your two tables.
    Attached Files Attached Files

  6. The Following User Says Thank You to MartinM For This Useful Post:

    ab2537 (2013-02-01)

  7. #6
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts
    The 2,316.38 is the balance that I must allocate into the funds. It's not a sum balance.

    So, there's no other way around this other than using a Macro? I don't use them.
    Last edited by ab2537; 2013-02-01 at 13:41.

  8. #7
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi

    I do not quite follow the meaning of 2316.38 as Martin pointed out. Are you trying to return all the value per the lookup! See the attached, might be of some help!
    Attached Files Attached Files

  9. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    Worksheet formulas can generally only put results into single cells.

    It is possible to get the multiple cells populated - as Kevin has illustrated.

    "I don't use them". May I suggest that it might be easier to learn how to write a simple Macro than to go for the intensely complex formulas needed to do the job ? I taught myself, mostly by recording Macros and seeing what they did, then polished them up with much help from this Lounge. Just a thought.
    Last edited by MartinM; 2013-02-01 at 14:35.

  10. #9
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Follow up

    @MartinM, It's a great suggestion! I've always avoided, will give it a try. Thanks.

    Kevin@Radstock. What you provided is perfect! The results are as I desired. I attempted to copy into my original spreadsheet and change the range, but it won't work? Any ideas why it's not working?

  11. #10
    Star Lounger
    Join Date
    Oct 2012
    Posts
    55
    Thanks
    1
    Thanked 10 Times in 10 Posts
    Hi ab2537

    I forgot to add to post #7 that it is an Array formula. You need to CTRL + SHIFT + ENTER to enter the formula, not just enter, then copy across and down.

    Kevin

  12. #11
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts
    Thanks, Kevin.

Posting Permissions

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