Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Both Lookup and Index...Match will return a value which is to the left of the list,
    but this only return the first value it find.

    What will be the formula to return more than one instances on the left of a list with a single lookup value
    I can't figure this out.

    see attached sample, Sheet1 is a list and Sheet2 show the result

    TIA
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does Steve (sdckapr)'s VlookupAll function from post 395235 do what you want?

  3. #3
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='773689' date='05-May-2009 03:46']Does Steve (sdckapr)'s VlookupAll function from post 395235 do what you want?[/quote]


    I tried the =VLIndex but it doesn't do what I want. It return the first instance only
    Am not sure if I have did it correctly

    see Sheet2 of the attached
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You used the formula incorrectly, but I'm afraid that I don't understand what you want to accomplish.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    I tried the =VLIndex but it doesn't do what I want. It return the first instance only
    Am not sure if I have did it correctly
    Try……..

    Sheet2!B2, entered the array formula and copied down

    {=IF(A2<>"",INDEX(Sheet1!A:A,SMALL(IF(Sheet1!C$2:C $10=A2,ROW(A$2:A$10)),COUNTIF(A$2:A2,A2))),"")}


    Regards
    Bosco

Posting Permissions

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