vlookup or if statement help (?)

[ljbrown]

Moderators: Please forgive me if I posted this query in the wrong place. I am unsure if it belongs under the Excel Forum as it deals with formatting cells or the Excel Formulas & Functions as it is needed for a countifs statement. Please forgive me in advance if I have posted this in the wrong location and please relocate it to the correct place. In addition, I apologize if this has been posted before. I have searched the forum and google for many hours before resorting to post on this forum. If it has been posted before, please point me in the right direction.

I have a list of staff members and I need to a formula to help list which staff are due for renewal. I have the data organized in a spread sheet with employee name and when they completed training. I would like to be able to enter a date under and generate a list to say who's training has expired before this date. Is it possible to include blank cells for those new hire staffs that have not yet been to training or is unknown?

I am attaching an abridged spreadsheet. i placed the data on the tab marked staff. I then have tabs for each discipline that we would like to list each employee that needs to attend class.

Currently we sort, resort, and resort again to find the information but it can be cumbersome. i think the answer lies within a vlookup or if statement. I was able to do something with an if statement, but i have to copy and paste staff names to the left. My issues with that is that staff fluctuates (retire, resign, new hire...) Id there a way to pull the name from the other tab as well to maintain integrity?

I have tried and tried but am having an issue getting my head wrapped around this.

Any and all help is appreciated.

Lance

[southward]

Hi,

I am pretty sure there is a way to write the formula you are looking for.
The best I have come up with only returns one name.
There is a way to modify an INDEX MATCH formulas to return multiple results.

Here is my formula for the BLS sheet.

Please Login or Register  to view this content.

I am sure there is a Guru out there that can modify this to return many. I know it calls for using the SMALL and ROW functions. I just can't wrap my head around it.

In the attached version of your file I am also providing an alternate method of getting the results you appear to be asking for.
It involves restructuring your data and then use a Pivot Table with Slicers.

The restructure and Pivot Table is on the "with Pivot Table" sheet.
The Names and Qualifications should be easy to set up. I used an INDEX MATCH MATCH to pull the Due Dates from your initial array.

Please Login or Register  to view this content.

If you go with the restructure option to use the Pivot Table to return your data, the above formula will make it easy to pull the dates from your array.

Take the new process for a spin. Let me know if you have any issues or questions.

Hope this makes sense and is helpful.

Cheers