Help with Index Match & Multiple Criteria

[sseagroves]

I am making a table that reads from an list of employees. I have attached a sample sheet (changed the names). The list includes Name, 2014 Start Date, Pay Type, Job Title, Location, Weeks, Hours, Avg. Hours.

I need a formula that will list employees on a separate sheet based on the following criteria:

2014 Start Date = 1/1/2014
Avg. Hours >=30

I have worked with a number of INDEX MATCH combinations but I cannot get the formula to quit at 30 Avg. Hours whenever I drag the formula down. I also have seen some employees that average over 30 hours get "skipped" over whenever I drag the formula down.

Somebody PLEASE HELP ME!

Thanks!

[oeldere]

With an helpcolumn.

after that i used a pivot table for the result.

see the attached file.

[sseagroves]

Thanks, but I also need their store number to show up...

[oeldere]

And the storenumber added in the file.

[micope21]

Hi

If you don't want pivot table for the result?

Use this
CurrentEligible sheet cell I2

Please Login or Register  to view this content.

copy down

EmployeeList sheet cell A2

Please Login or Register  to view this content.

Copy down and cross

Regard
micope21

[sktneer]

Which Excel version you are using? I assume it is 2007 or higher.
Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. after placing the formula in the cell, hold down the Ctrl+Shift and then press Enter)

In A3

Please Login or Register  to view this content.

then drag across to H3 and then down until you get blank cells.

If you are using excel version 2003, you will find a formula for that too in the attached sheet on a different table (light green color).