+ Reply to Thread
Results 1 to 19 of 19

how do I use vlookup for multiple occurrences of the same value

  1. #1
    bj
    Guest

    RE: how do I use vlookup for multiple occurrences of the same value

    I'm sorry that I didn't answer your question.
    vlookup will only show the first occurance . You will need something more.

    "Edith F" wrote:

    > I am using the vlookup function to check a table which may or may not have
    > multiple rows for the same value of the column I am using to select. So far,
    > everything I have tried keeps giving me the first occurrence it finds. Do I
    > need to add additional parameters or should I be using something other than
    > vlookup?


  2. #2
    Edith F
    Guest

    how do I use vlookup for multiple occurrences of the same value

    I am using the vlookup function to check a table which may or may not have
    multiple rows for the same value of the column I am using to select. So far,
    everything I have tried keeps giving me the first occurrence it finds. Do I
    need to add additional parameters or should I be using something other than
    vlookup?

  3. #3
    Alan Beban
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Edith F wrote:
    > I am using the vlookup function to check a table which may or may not have
    > multiple rows for the same value of the column I am using to select. So far,
    > everything I have tried keeps giving me the first occurrence it finds. Do I
    > need to add additional parameters or should I be using something other than
    > vlookup?


    If you have the functions in the freely downloadable file at
    http:/home.pacbell.net/beban available to your workbook you can use the
    VLookups function:

    =VLookups(lookup_value,Lookup_table,column_reference) array entered into
    enough vertical cells to accommodate the number of occurrences of
    lookup_value. Or, to avoid array entering:

    =Index(VLookups(lookup_value,Lookup_table,column_reference), Row(A1))
    filled down as far as required.

    Alan Beban

  4. #4
    Bernd Plumhoff
    Guest

    RE: how do I use vlookup for multiple occurrences of the same value

    Hi Edith,

    maybe my function vlookupall() at
    http://www.sulprobil.com/html/vlookupall.html can help you.

    HTH,
    Bernd

  5. #5
    Harlan Grove
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Alan Beban wrote...
    ....
    >If you have the functions in the freely downloadable file at
    >http:/home.pacbell.net/beban available to your workbook you can use

    the
    >VLookups function:

    ....

    Yes, but this could be done with built-in formulas. If the source range
    were named Tbl, the lookup value were in cell G1, and the topmost
    result in cell H1 with other results to appear below it in col H, the
    following formulas would work.

    H1:
    =VLOOKUP(G1,Tbl,2,0)

    H2 [array formula]:
    =IF(COUNTIF(INDEX(Tbl,0,1),G$1)>ROW()-ROW(H$1),
    OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=G$1,ROW(Tbl)-CELL("Row",Tbl)),
    ROW()-ROW(H$1)+1),1,1,1),"")

    Fill H2 down as far as needed.


  6. #6
    Harlan Grove
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Alan Beban wrote...
    ....
    >If you have the functions in the freely downloadable file at
    >http:/home.pacbell.net/beban available to your workbook you can use

    the
    >VLookups function:

    ....

    Yes, but this could be done with built-in functions. If the source
    range were named Tbl, the lookup value were in cell G1, and the topmost
    result in cell H1 with other results to appear below it in col H, the
    following formulas would work.

    H1:
    =VLOOKUP(G1,Tbl,2,0)

    H2 [array formula]:
    =IF(COUNTIF(INDEX(Tbl,0,1),G$1)>ROW()-ROW(H$1),
    OFFSET(Tbl,SMALL(IF(INDEX(Tbl,0,1)=G$1,ROW(Tbl)-CELL("Row",Tbl)),
    ROW()-ROW(H$1)+1),1,1,1),"")

    Fill H2 down as far as needed.


  7. #7
    Alan Beban
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>If you have the functions in the freely downloadable file at
    >>http:/home.pacbell.net/beban available to your workbook you can use

    >
    > the
    >
    >>VLookups function:

    >
    > ...
    >
    > Yes, but this could be done with built-in formulas.


    Indeed, as you demonstrated, though it's not clear why that would be
    desirable.

    The particular formula you provided is slower than the array entered
    VLookups formula when the number of recalculations on a sheet gets
    relatively large.

    I wonder how a user would test where the crossover in speed occurs so
    he/she could get some guidance on which works best in his/her
    application. I suppose just try them and see if there's a noticeable
    difference.

    Or, of course, if one just has a predisposition for built-in formulas
    without regard for efficiency, then there you have one.

    Alan Beban

  8. #8
    Harlan Grove
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Alan Beban wrote...
    ....
    >The particular formula you provided is slower than the array entered
    >VLookups formula when the number of recalculations on a sheet gets
    >relatively large.

    ....

    There are situations in which Excel workbooks can't use any VBA, so
    it's good to know how to do certain tasks using no VBA. We may disagree
    about this, but IMO it's best to avoid VBA for anything that can be
    done compactly with built-in functions and defined names. Note the
    fuzzy term 'compactly'.

    On the other hand, if recalc performance is absolutely critical, better
    to use 2 formulas/cells per each result plus one extra formula/cell.

    G2:
    =ROWS(Tbl)

    H1:
    =VLOOKUP(G$1,Tbl,2,0)

    I1:
    =MATCH(G$1,INDEX(Tbl,0,1),0)

    H2:
    =INDEX(Tbl,I2,2)

    I2:
    =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)

    I guarantee you this will run recalc circles around your VLookups
    formulas. Benchmark results available upon request.


  9. #9
    Alan Beban
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>The particular formula you provided is slower than the array entered
    >>VLookups formula when the number of recalculations on a sheet gets
    >>relatively large.

    >
    > ...
    >
    > There are situations in which Excel workbooks can't use any VBA, so
    > it's good to know how to do certain tasks using no VBA. We may disagree
    > about this, but IMO it's best to avoid VBA for anything that can be
    > done compactly with built-in functions and defined names. Note the
    > fuzzy term 'compactly'.
    >
    > On the other hand, if recalc performance is absolutely critical, better
    > to use 2 formulas/cells per each result plus one extra formula/cell.
    >
    > G2:
    > =ROWS(Tbl)
    >
    > H1:
    > =VLOOKUP(G$1,Tbl,2,0)
    >
    > I1:
    > =MATCH(G$1,INDEX(Tbl,0,1),0)
    >
    > H2:
    > =INDEX(Tbl,I2,2)
    >
    > I2:
    > =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)
    >
    > I guarantee you this will run recalc circles around your VLookups
    > formulas. Benchmark results available upon request.
    >

    Which of the formulas, if any, are to be array entered?

    Which get copied where to display the output?

    Alan Beban

  10. #10
    Harlan Grove
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    "Harlan Grove" <[email protected]> wrote...
    ....
    >I2:
    >=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)

    ....

    Oops, make that

    I2:
    =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1



  11. #11
    Harlan Grove
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    "Alan Beban" <[email protected]> wrote...
    >Harlan Grove wrote:

    ....
    >>On the other hand, if recalc performance is absolutely critical, better
    >>to use 2 formulas/cells per each result plus one extra formula/cell.
    >>
    >>G2:
    >>=ROWS(Tbl)
    >>
    >>H1:
    >>=VLOOKUP(G$1,Tbl,2,0)
    >>
    >>I1:
    >>=MATCH(G$1,INDEX(Tbl,0,1),0)
    >>
    >>H2:
    >>=INDEX(Tbl,I2,2)
    >>
    >>I2:
    >>=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)

    ....

    I already mentioned I screwed up the I2 formula. It should be

    =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1

    >Which of the formulas, if any, are to be array entered?


    None. You couldn't test to be sure? You're not sufficiently familiar with
    Excel to know yourself? Just a rhetorical question for the benefit of other
    readers, and you prefer that device to simply stating none of them need to
    be entered as array formulas?

    >Which get copied where to display the output?


    I considered my previous response an extension of my response before that,
    so implicit to drag the formulas in row 2 down until they return error
    values. I must endeavor to remember that you need everything explicit.



  12. #12
    Alan Beban
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Harlan Grove wrote:
    > "Alan Beban" <[email protected]> wrote...
    >
    >>Harlan Grove wrote:

    >
    > ...
    >
    >>>On the other hand, if recalc performance is absolutely critical, better
    >>>to use 2 formulas/cells per each result plus one extra formula/cell.
    >>>
    >>>G2:
    >>>=ROWS(Tbl)
    >>>
    >>>H1:
    >>>=VLOOKUP(G$1,Tbl,2,0)
    >>>
    >>>I1:
    >>>=MATCH(G$1,INDEX(Tbl,0,1),0)
    >>>
    >>>H2:
    >>>=INDEX(Tbl,I2,2)
    >>>
    >>>I2:
    >>>=MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)

    >
    > ...
    >
    > I already mentioned I screwed up the I2 formula. It should be
    >
    > =MATCH(G$1,OFFSET(Tbl,I1,0,G$2-I1,1),0)+I1
    >
    >
    >>Which of the formulas, if any, are to be array entered?

    >
    >
    > None. You couldn't test to be sure? You're not sufficiently familiar with
    > Excel to know yourself? Just a rhetorical question for the benefit of other
    > readers, and you prefer that device to simply stating none of them need to
    > be entered as array formulas?


    No. You gave me a screwed up formula for I2 and I was just trying to see
    why what you provided wasn't working as you suggested it would.
    >
    >
    >>Which get copied where to display the output?

    >
    >
    > I considered my previous response an extension of my response before that,
    > so implicit to drag the formulas in row 2 down until they return error
    > values.



    I already did that with the only formula you provided for I2 and it
    produced garbage; so yes indeed, I and any one else trying to use what
    you provided needed something more explicit. Our fault, of course.

    > I must endeavor to remember that you need everything explicit.


    No; just endeavor to test your stuff before you post it so you won't
    have to embarrass yourself by trying to shift the responsibility to me
    when it doesn't work.

    Alan Beban

  13. #13
    Harlan Grove
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    "Alan Beban" <[email protected]> wrote...
    ....
    >No; just endeavor to test your stuff before you post it so you won't
    >have to embarrass yourself by trying to shift the responsibility to me
    >when it doesn't work.


    I admit my own mistakes. I screwed up the I2 formula, and I didn't test it
    (I knew the technique works, but I failed to add the I1 value to I2).
    However, did you make a clear statement that it didn't work? No, you babble
    some oblique crap about which formula to array-enter and which to copy where
    to get the results.



  14. #14
    Alan Beban
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Harlan Grove wrote:
    > "Alan Beban" <[email protected]> wrote...
    > ...
    >
    >>No; just endeavor to test your stuff before you post it so you won't
    >>have to embarrass yourself by trying to shift the responsibility to me
    >>when it doesn't work.

    >
    >
    > I admit my own mistakes. I screwed up the I2 formula, and I didn't test it
    > (I knew the technique works, but I failed to add the I1 value to I2).
    > However, did you make a clear statement that it didn't work? No, you babble
    > some oblique crap about which formula to array-enter and which to copy where
    > to get the results.
    >
    >


    How noble of you to admit your own mistakes while refusing to take
    responsibility for the confusion they directly caused. I was simply
    trying to exhaust the possibility that there was something I wasn't
    understanding about the use of your formulas before saying that it was
    the formulas themselves that were screwed up. But that seems to be a
    little too subtle for you to comprehend.

  15. #15
    Harlan Grove
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    "Alan Beban" <[email protected]> wrote...
    ....
    > . . . I was simply
    >trying to exhaust the possibility that there was something I wasn't
    >understanding about the use of your formulas before saying that it was
    >the formulas themselves that were screwed up. But that seems to be a
    >little too subtle for you to comprehend.


    Unadulterated BS.

    The formula didn't work. Presumably you figured that out, but did you want
    to say that? Much less venture a fix?



  16. #16
    Alan Beban
    Guest

    Re: how do I use vlookup for multiple occurrences of the same value

    Harlan Grove wrote:
    > "Alan Beban" <[email protected]> wrote...
    > ...
    >
    >>. . . I was simply
    >>trying to exhaust the possibility that there was something I wasn't
    >>understanding about the use of your formulas before saying that it was
    >>the formulas themselves that were screwed up. But that seems to be a
    >>little too subtle for you to comprehend.

    >
    >
    > Unadulterated BS.
    >
    > The formula didn't work. Presumably you figured that out, but did you want
    > to say that? Much less venture a fix?
    >
    >


    At the time I had *not* figured that out. I was still trying to figure
    out whether it was that the formulas could not work (which would imply
    that without so advising the users you posted without testing--not the
    most likely probability in my mind at the time), or that I was applying
    the formulas inappropriately; hence the questions about array entering
    and copying. I wouldn't consider venturing a "fix" unless and until I
    knew it was the formulas themselves and not my particular attempt to
    apply them that was the problem.

    But that's OK; rant on, it's instructive for the users.

  17. #17
    Registered User
    Join Date
    05-04-2009
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: how do I use vlookup for multiple occurrences of the same value

    Vlookup provides us with an option of searching for the first string only. But in case we need to go for the second or third or may be last entry of the value we can use a combination of few other functions of excel.

    Below is the formula, which we can use for the above purpose;
    (as this is an array formula so please hold ctrl and shift key while entering it)
    =index($a$1:$b$25,large(($a$1:$a$25=e2)*row($a$1:$a$25),countif($a$1:$a$25,e2)-f2+1),2)

    attached is the file for the same.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    09-29-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: how do I use vlookup for multiple occurrences of the same value

    You people are brilliant! I am having a hard time with a formula. I want to use a VLOOKUP to return the sum of multiple occurrences of the same value. For example...
    Tom 3
    Jerry 4
    Tom 3
    Mike 5
    Jerry 2
    Tom 6
    So, I am trying to do a lookup in one sell searching for Tom and instead of only returning 3 (because it is the first occurrence), I would like it to add 3,3,6 and return 12. Is there a way to do this?

  19. #19
    Registered User
    Join Date
    10-13-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Need to lookup value in an array while satisfying a condition

    Modeling for a film. Need to essentially find a film's genre in a table and match the given box office level to a set range of box office levels in order to determine marketing spend for a film
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1