Results 1 to 7 of 7

vlookup more than one result

This is a discussion on vlookup more than one result within the Excel Questions forums, part of the Question Forums category; Hello could you please help me I need a forumla that uses a vlookup to find multiple results for the ...

  1. #1
    Board Regular
    Join Date
    Nov 2011
    Posts
    167

    Default vlookup more than one result

    Hello

    could you please help me I need a forumla that uses a vlookup to find multiple results for the same part i.e

    I have parts that appears more than once all for the same Qty

    part qty date
    apple 100
    apple 100
    apple 100

    on another sheet i have the follwoing
    part qty date
    apple 100 03/05/13
    apple 100 16/05/13
    apple 100 23/05/13

    what I would like is a vlookup to return the first result then for the second return the second result and so on?

    I hope this makes sense.


    cheers

  2. #2
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,785

    Default Re: vlookup more than one result

    not clear

    second sheet

    part qty date
    apple 100 03/05/13
    apple 100 16/05/13
    apple 100 23/05/13


    why no;t just sort whole data acording to thridcolumn ascending.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  3. #3
    Board Regular
    Join Date
    Nov 2011
    Posts
    167

    Default Re: vlookup more than one result

    I have 780 lines with 78 duplicated parts

  4. #4
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,785

    Default Re: vlookup more than one result

    I suppose even then sorting will not take time. sort according to part and date.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  5. #5
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,246

    Default Re: vlookup more than one result

    Quote Originally Posted by phairplay View Post
    I have 780 lines with 78 duplicated parts
    Excel 2010
    ABC
    1partqtydate
    2apple1003/5/2013
    3pwar10016/05/13
    4apple10023/05/13
    5
    6
    7apple10016/05/13
    8
    9apple1001/1/2012
    10

    Sheet11




    Excel 2010
    AB
    1Apple
    23/5/2013
    323/05/13
    416/05/13
    51/1/2012
    6
    7
    8
    9

    Sheet12



    Array Formulas
    CellFormula
    A2{=IFERROR(INDEX(Sheet11!$C$2:$C$11,SMALL(IF(Sheet11!$A$2:$A$11=$A$1,ROW(Sheet11!$C$2:$C$11)-ROW(Sheet11!$C$2)+1),ROWS(Sheet11!$A$2:$A2))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  6. #6
    Board Regular
    Join Date
    Nov 2011
    Posts
    167

    Default Re: vlookup more than one result

    Spot on many thanks

  7. #7
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,246

    Default Re: vlookup more than one result

    Quote Originally Posted by phairplay View Post
    Spot on many thanks
    You are welcome.
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com