Returning several values from a column if they're a match

[Ranu]

Hey guys,

Here is the thing:
I've got this table where I have 2 columns. One that contains TEXT (name of the task that needs to be done), one that contains DATE (due date of the task).

I'd like to be able to see "what tasks need to be done" by typing in a specific cell their due date.
Just to help you understand, let's name "Task one" T1, "task two" T2, etc.

T1, T2, T5 and T6 are due on 01/01/2012
T3 and T4 are due on 02/02/2012

If, in my cell I type "01/01/2012" then it will return me the list of the tasks that have to be done on THIS specific day like that :
T1
T2
T5
T6
(I don't care much in which order, I just want it to be a list. Each task in a separate cell)

I've tried using the "lookup" function and telling the function to look for the value I enter in my cell in DATE and then return me the value from the same line in the TEXT column, but I'm stuck. The lookup function returns only ONE value (since it's in ONE cell), if I type the same function all the way, it always return the same value. Basically, I'm currently stuck if I have several possible values.

I'd either need to be able to tell my lookup function "Hey buddy, don't return me the same result twice. If this one is already listed above, then find the next one" or I need to just find another way around.

Any thoughts on this ?
Thanks in advance, and I hope I was clear enough in my explanation!

[Richard Buttrey]

Hi,

Have you considered Data Filter? A standard Autofilter (where the list is filtered in place) is probably sufficient, but don't forget you also have the Advanced Filter where you can extract data that meets criteria you select to another range.

Regards

[canapone]

Hi,

filter is the most convenient approach to your request, a formula could be (in E2 the reference date)

=IF(ISERROR(INDEX(A$2:A$100,SMALL(IF($B$2:$B$100=$E$2,ROW($A$2:$A$100)-1,""),ROWS($A$2:A2)))),"",INDEX(A$2:A$100,SMALL(IF($B$2:$B$100=$E$2,ROW($A$2:$A$100)-1,""),ROWS($A$2:A2))))

To be confirmed with control+shift+enter and to be copied down.

More or less it's like a VLOOKUP for first-second...occurrencies.

Hope it helps.

Regards

[Marcol]

Try this workbook

In D2

Please Login or Register  to view this content.

In D3, given you are using 2007 or above, this array formula (Cheers DLL)

Please Login or Register  to view this content.

Confirrm with Ctrl+Shift+Enter not just Enter
Drag/Fill Down as required

Then create dynamic named ranges as per this workbook

In the Sheet Module

Please Login or Register  to view this content.

Select from the drop-down in G2 to get the result.

Hope this helps

[Ranu]

Hey guys, thank you guys for your help, it's working just fine now!

Also :

@R.Buttrey : Yeah, I had never tried using the filter option before. It is indeed very useful and I'm now using it. I have other columns I didn't mention and this allows me to display the tasks based on other criterias, so it's pretty nice.

@Canapone : Your formula is the one I'm using. Works like a charm. Thanks a lot. I also implemented the filers on the original columns. Grazie mille di avermi aiutato (I hope that's correct).

@Marcol : Thanks for your help, but I'm going to stick with the 2 previous solutions that were given, even more so as they seem less complicated! Hehe.

Take care guys, thanks again for your help