lookup with variable number of matches? (Excel 200

Topic

New Reply

Greetings!

I have a long, ***long*** list of numbers with matching dates (they’re hymns, and the date 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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

[indent]

---

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

---

[/indent]

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

Reply | Quote

[indent]

---

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

---

[/indent]

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote