if statement false then look in next cell

[tacape]

example.xlsI have a spreadsheet with 2 sheets and I am trying to come up with a formula to list the names under the appropriate date. So if John Smith has 08/01/2012, I want John Smith's name to return into the first available row. The next name should be the next one that has a date of 08/01/2012. I have tried =IF($I$3=INDEX('Benefit Eligible'!$O$2:$O$295,MATCH($I$3,'Benefit Eligible'!$O$2:$O$295,0)+MOD((ROW()-2),295)),INDEX('Benefit Eligible'!$B$3:$B$296,MATCH($I$3,'Benefit Eligible'!$O$2:$O$295,0)+MOD((ROW()-2),295)),"") but it is leaving blank cells. Does anyone have any ideas?

[tigeravatar]

tacape,

Attached is a modified version of your example workbook.
In sheet 'Detail' cell B4 and copied over and down to Q23 is formula:

Please Login or Register  to view this content.

Note that the formula will only work properly if it is guaranteed that there are never duplicates on any given date like in your example. If there are duplicate names on a date and you need them returned also, let me know.

[tacape]

It works pefectly. Thank you.

[tigeravatar]

tacape,

You're very welcome

If that takes care of your need, please mark the thread as Solved:
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save