Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    lookup with variable number of matches? (Excel 200

    Greetings!

    I have a long, ***long*** list of numbers with matching dates (they're hymns, and the date[s] sung, for the past ten years, where I work). I want to make a nice little box on the front worksheet so that my boss the pastor can enter a hymn number and voila, a list of dates used will pop up, nicely formatted, underneath. I have fiddled with pivot tables, lookups, and other things, and nothing looks quite right - mainly because there are hundred and hundreds of these hymns, some have been used not at all, some a time or two, and some are used over and over again (communion hymns, for example). Is there a good way to do this? I'm almost tempted to say that I'd be better off using Access, but the church's computer doesn't have it...

    Many thanks for any suggestions or help!

    Regards
    Mary B.

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

    Re: lookup with variable number of matches? (Excel 200

    Have you considered AutoFilter? Very easy to set up and use, and effective. See attached example.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup with variable number of matches? (Excel 200

    One of the problems with Autofilter is that the dropdown box has a limit of 1000 unique values, so if you have more than 1000 hymn numbers, it won't be so easy for your boss to pull them up.

    I've attached a worksheet that uses the LARGE function. You type the hymn number in the yellow cell and then cells G4:G8 contain the 5 largest (i.e. most recent) dates that the hymn was used. You can copy the function more than 5 times if you want the 20 most recent or whatever. Each formula in G4:G8 is an array formula so you need to press ctrl-shift-enter to complete the formula.

  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 with variable number of matches? (Excel 200

    <hr>One of the problems with Autofilter is that the dropdown box has a limit of 1000 unique values<hr>

    To me this isn't a problem. Who wants to scroll through thousands of entries?

    I make sure that if my "full lists" are ever this long, I create some "sublists" (logical groupings, by first letters, etc) with extra columns to break up the large list. I find it is better to have to click 1 or 2 sublists than to try to scroll through a huge list. Smaller "bites" of the list is better.

    Steve

  5. #5
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: lookup with variable number of matches? (Excel 200

    Chipshot;

    I like the formula. I added a slight adjustment. If the hymn was never sung or maybe only once or twice, etc., the result would be a 0 or if a date format is set you would get 1/0/00. My addon to your work stops this from occurring and leaves the result blank. See attached


    yoyoPHIL

Posting Permissions

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