Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup more than one row (2000)

    I want a lookup feature (perhaps using VLOOKUP) to look-up a date in column A of a table.. BUT I need it to return not just information from the first row where the date occurs but ALL rows where it occurs. For example, the same date may occur in up to four of the rows in the table. Any ideas? Thanks, Andy.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup more than one row (2000)

    Formulas can only return 1 value.
    HLOOKUP,VLOOKUP and MATCH only return the first matching item

    Filtering seems the best choice (auto or Adv)

    Other options:
    You could write a macro to do it, you could create a defined function to create an array of all the rows and output it as an array using 1 formula.

    You could modify the data, to create a unique entry and then use an array formula.

    It depends on what your goal is and how you want to use the info. Could you elaborate on your goals and I could provide more details.

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup more than one row (2000)

    I have a column of dates (A) and other data in columns B, C, D,etc. On a different sheet someone can enter a date and I want it to then display (on the same sheet) the data from columns B, C, D for that specific date. The same date can occur for between 1 and 4 records/rows in the main table.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup more than one row (2000)

    I think the VLIndex function, I provided, will probably suit your needs.

    Steve

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup more than one row (2000)

    Hi Steve,

    I've only just discovered your great lookup functions. I have a question.

    If the data in the lookup range changes will the lookup function (specifically the VlookupAll) update its results? I've been experimenting & it appears that it will only updates the result if I edit the formula. Is there a way I could get it to recalculate? The spreadsheet is set to autocalc (though I don't think that is relevant for macros?)

    EDIT: I am referring to changing the result data (the 3rd parameter)

    Thanks

    Matthew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup more than one row (2000)

    It updates automatically for me. Could you attach a sample workbook in which the problem occurs?

  7. #7
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup more than one row (2000)

    Hi Hans,

    Attached is a sample.

    If I change the "result" column ([img]/forums/images/smilies/cool.gif[/img] it does not update the "lookup function" column (D). For example if I change cell B4 from "a" to "z", cells D8 & D9 remain unchanged. ie. "a".

    I can get around that problem by filling down the formula in column D, effectively recalculating.

    Thanks for your help,

    Matthew
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup more than one row (2000)

    If you change the formula in cell D3 to this:

    <code>
    =vlookupall(C3,$A$3:$B$10,1)
    </code>

    and then copy it down, then it will update when you change the result column.
    Legare Coleman

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Lookup more than one row (2000)

    This is because you didn't include column B in the lookup range. Hence Excel doesn't "know" it should recalculate the formula if you change a cell in column B. If you change the formula to

    =vlookupall(C3,$A$3:$B$10,1)

    it'll update correctly.

  10. #10
    Star Lounger
    Join Date
    May 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup more than one row (2000)

    Thanks Hans,

    I hadn't thought about that. It makes sense. This is my first application of a function so I am still learning.

    Regards,

    Matthew

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup more than one row (2000)

    Edited by HansV to restore attachment that had got lost in the server crash of 2007

    The attached functions can be added to a module in personal.xls (or any other workbook) and they then can be used.

    The first one attached is "VLookupAll" and is similar to Vlookup. You would use it like:
    <pre>=Vlookupall("a",A1:A52, 5)</pre>

    to give all the items in Col F (5 cols from A) that have the value in Col A = "a". Each will be separated by a comma and a space (default).
    You can change the separator to semicolon using the 4th "optional parameter"
    <pre>=Vlookupall("a",A1:A52, 5,";")</pre>

    or even use a "linefeed character" (like hitting <alt><enter> in a cell:
    <pre>=Vlookupall("a",A1:A52, 5,char(10))</pre>


    Unlike Vlookup, the column number is an OFFSET. Vlookup goes from 1 (the column being "looked up") to the last column in the defined range. vlookupall can take negative columns (the column being looked up is ZERO). Negative numbers are allowed. The range is such that the Col Number of lookup + Col offset must be from 1 to 255. These Lookups also are all EXACT matches.
    <pre>=Vlookupall("a",C1:C52, -2)</pre>

    to give all the items in Col A (2 cols from C) that have the value in Col C = "a". Each will be separated by a comma and a space (default).

    The second one is the analogous HLookupAll function which looks in rows rather than columns.

    In either case, if you do NOT want a comma and space (, ) to be the default, you can change it in the function to something else!

    The third and fourth codes are to give you a separate cell for each "found" value:
    <pre>=VLIndex("a",A1:A52, 5,2)</pre>

    to give the 2nd item in Col F (5 cols from A) that have the value in Col A = "a".
    If you want to put them down a row you could enter something in A1 something like:
    <pre>=VLIndex("a",$A$1:$A$52, 5,row())</pre>

    and copy it down the row and you will get the list.

    Like the 2 earlier ones, negative column offsets are allowed.

    HLIndex is the analogous row lookup.

    If you find any problems with these let me know. I haven't tested them "to death" but they seem to do what they are meant to.

    Steve
    Attached Files Attached Files

  12. The Following User Says Thank You to sdckapr For This Useful Post:

    mojave1 (2012-06-29)

Posting Permissions

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