Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index Match Function on Merged Cells

    Hello Everyone,

    I am trying to use Index Match Function. Sample data attached for your reference.

    Example: In Table 2, I need the score of Jerry's Name with function A,B,C & D from the Table 1. Hope I'm able to explain. Its working fine if I unmerge the cells into Table 1 but not if it's merge. Could there be any solution for that. Actually I can't unmerge the cells as the Table 1 is client data which I can't change.

    Any help would really be appreciated. Thanks in advance.

    Cheers,
    AJ
    Attached Files Attached Files
    Last edited by abhi_jain80; 2016-07-28 at 09:54.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi AJ

    see attached file.
    This uses 'helper columns'.
    I've used index-match rather than vlookup for the formula.

    I note that you have used 'first name' for your Table 2 lookup condition.
    If this is not what you really want, we will have to look at other methods.
    But my formulas DO give the required values you have asked for.

    zeddy
    Attached Files Attached Files

  3. #3
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Zeddy for the solution. But actually I can't unmerge or change the data as it is client table.

    Best Regards,
    AJ

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi AJ

    My solution didn't unmerge or change the data.
    ..but it did add 'helper columns' and formulas.
    These helper columns could be placed far to the right, could be hidden, and could be added and then removed later via VBA if required.
    The helper columns and formulas could even be placed on a different worksheet.

    So, what are you actually allowed to do on the sheet?

    zeddy

  5. #5
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Zeddy for quick response.

    Actually I can only copy the data from the client sheet. I can't even add or delete any columns. Just I can use the data for my purpose.

    Best Regards,
    Abhishek

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi AJ

    So, you could have your own workbook, then import the data from the workbook with the client sheet and Table 1.
    Your workbook could then have an additional Table 2, where you can enter required lookup data??

    zeddy

  7. #7
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Zeddy,

    Actually the client sheet is changing on daily basis & saved on shared folder. Would u mean that I make the replica of the client sheet on my drive which get updated every time & do the lookup from the replica sheet.

    Best Regards,
    Abhishek

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 480 Times in 457 Posts
    Hi AJ

    ..that's exactly what I mean.
    If the workbook name, containing the client sheet, stays the same workbook name on the shared folder, that would be easy to deal with..
    e.g. \\somesource\somefolder\someworkbook.xlsx

    If the workbook name on the shared folder is updated with a different workbook name, we can deal with that if we know what the naming rules are
    e.g. \\somesource\somefolder\someworkbook-yyyy-mm-dd.xlsx

    Your workbook could have a vba routine assigned to a 'button'
    e.g. [fetch latest Client data from shared folder]
    This routine would import the Table of Client data etc etc from the shared folder.
    Your Table 2 lookups in your workbook file would then work automatically with the imported latest data.
    You could also automatically load a copy of the latest Client data Table into your workbook whenever you open your workbook i.e. it fetches the latest data whenever you load your workbook.

    zeddy
    Last edited by zeddy; 2016-07-28 at 11:21.

  9. #9
    Lounger
    Join Date
    Jun 2016
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many Thanks Zeddy. I got it. Its working.

    Best Regards,
    Abhishek

Posting Permissions

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