Search a string of info and return mutliple results

[slindfors]

Not the best way to describe this one...

Column O contains the names of operators that recur down the list.
Column P contains the dates that they must perform duties

The chart to the right wants to capture all the dates that each operator is assigned duties so they may easily see when they "on." So, operator A would look in the highlighted row and know when they are working.

[azumi]

SOmething like this? Using Array Formulas (you need to press CTRL-SHIFT-ENTER button together, not ENTER alone) and copied down and cross

[Tony Valko]

Try this array formula** entered in S4:

=IFERROR(INDEX($P:$P,SMALL(IF($O$4:$O$51=$R4,ROW(O$4:O$51)),COLUMNS($S4:S4))),"")

Format as Date

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to S13 then across until you get a column full of blanks.

[slindfors]

That is just about it. The only issue that I had was that the first date entered would not change when the operator was changed.

I've attached a new spreadsheet without all the extraneous stuff. I left the formulas in place and they now have a #ref error due to the missing columns, which brings up a question: What does the reference, $A$1:A1 for instance, at the end of the formula do for the formula? A1 did not seem to me to be of any importance since it did not seem relevant to the desired result. Clearly, I was wrong! I would like to understand that, though.

[Tony Valko]

What does the reference, $A$1:A1 for instance, at the end of the formula do for the formula?

Not sure who you're replying to...

There is no reference to $A$1:A1 in my suggestion so apparently you're not replying to me.

[sktneer]

Please find the attached sheet.

In D4 I have used another Array Formula to extract the sorted unique Names, so you don't need to manually type them.

In D4

Please Login or Register  to view this content.

And corrected the formula in E4 like this......

Please Login or Register  to view this content.

See if this is what you are trying to achieve.

[boopathiraja]

May be something like in the attachment...

[Tony Valko]

Here's your file from post #1 with the formula added.

Forum Example IV.1.xlsx