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

    Need to understand Index/Match and Vlookup can be nested.

    Hello,
    Below is the final results of what I need: The Begin and Ending Balance for 3 Fund Numbers that have different codes.

    I would key in the Fund Number and Code in Spreadsheet 1. I need a formula linked to a second spreadsheet named "Download" to apply the appropriate Beg/End Balance for each 4 Digit Code.

    Here's the formula I used..which didn't work
    =VLOOKUP($A19,MATCH(B19,' Download 4607'!$B$6:$K$664,0),6,FALSE)


    Fund Number code Beg Bal End Balance
    541705 2677 0.00 0.00
    541705 2061 384,912.00 212,068.56
    541705 2161 309,652.00 29,533.48




    Download
    Fund CODE Beg Bal End Balance
    541705 2677 0.00 0.00
    541705 2061 384,912.00 212,068.56
    541705 2161 309,652.00 29,533.48
    Attached Files Attached Files
    Last edited by ab2537; 2013-02-07 at 12:01. Reason: Add formula that I thought would work; need to determine if my reasoning is correct.

  2. #2
    Lounger
    Join Date
    Mar 2006
    Location
    Philadelphia, PA
    Posts
    30
    Thanks
    12
    Thanked 0 Times in 0 Posts
    I realized I can combine the two columns (Fund + code) making it a unique value and then use VLookup.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Yes, you have to do it that way.

    see attached example file.

    The most efficient way is to use MATCH and INDEX rather than VLOOKUP.
    Note that once you have matched the row number in the Download, you can use it to retrieve data from any column.
    So it is used twice: onece to get the Beg Bal, and then again for the End Balance.

    Using this method is far more efficient than using VLOOKUP for each value you want to return.

    zeddy
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    ab2537 (2013-02-07)

Posting Permissions

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