Formula to extract list of items in column that also meet 2 criteria in the same row

[PeteABC123]

Here's the situation: Looking for formula to extract a list of items from a column that also meet 2 criteria in the same row.

Have a list of ID #'s in col. D. Other items in those rows are Class (can be numbered 1-10), and Submitted (can be "Y" or "N")

So: looking to extract to col L a list of those ID's(from Col. D) who's class(in col. E) is less than 3, and who's Submitted(in col G) is equal to Y.

Example of model is attached.

Many thanks.

Pete

[JeteMc]

Try this array formula:

Please Login or Register  to view this content.

Paste it into cell L2, press the F2 key, press and hold Ctrl, Shift and Enter.
Let me know if you have any questions.

[PeteABC123]

JeteMc: Made a minor mod, and this seems to work (ROW(A2) became(A1). One thing. . .they're all in reverse order from how the list appears. Is there a painless way to put them in the order that was on the original list? Many Many thanks JeteMc

Please Login or Register  to view this content.

Pete

[JeteMc]

This puts the names in order.

Please Login or Register  to view this content.

Let me know if you have any questions.

[PeteABC123]

JeteMc: You are awesome! Where do I send the pizzas? Thanks again.

Pete

[JeteMc]

You're welcome and thank you for the feedback (I imagine their is a youth group somewhere near you that would like to get some pizzas or chocolates for Valentine's this Sunday). If you haven't already please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.