Indexing with duplicates

[kerui]

I am attempting to automate an updates worksheet so that specific departments can see how current and previous hiring processes are going.

Consequently I am attempting to index a region with duplicates and still see all distinct outcomes.

For our purposes I would like to see how we can pull out all the positions for the "claims" department. I can extrapolate / copy and paste for the other departments.

Thanks so much in advance!

Ker

Filled Positions by Department.xls

[NBVC]

Try formula in B4:

Please Login or Register  to view this content.

CSE confirmed and copied down and across (Note: you may need to make adjustments to INDEX range to get right column(s).

[NBVC]

Try formula in B4:

Please Login or Register  to view this content.

CSE confirmed and copied down and across (Note: you may need to make adjustments to INDEX range to get right column(s).

[kerui]

I'm experiencing complications with the date fields, I adjusted the necessary columns to J for "Date Open". Is there a complication with the formula or am I just being an idiot?

[NBVC]

What kind of complications... perhaps you need to format the result cells as Date (if you are seeing a number starting with 4)

[kerui]

You are a godsend my friend, thank you very much!

[kerui]

so I copied your fomula into word did a find and replaced on the word "filled" and replaced it with "open" to try to make it work for the other worksheet and I got an error... any thoughts?

[NBVC]

What kind of error?

You can do the replacement in Excel itself.. select the columns, then CTRL+H replace "Filled" with "Open"

Also, you will need to get rid of the #N/A values in column A of the 2 sheets (use IFERROR() to error trap).

[kerui]

Filled Positions by Department.xls

I don't understand

[kerui]

Oh wow, getting rid of the #N/A took care of it.
Thank you so much I'll mark as solved.