Excel 2007 : Match Values and Return "Present" "Absent" "Per Diem" or remain blank

Hello Excellent Excellers!

The attached document is a roster for employees that I'm working on. I want to be able to see, at a glance, which employees are "Absent" from one month to the next. So basically I've been provided a formula that matches the data from one cell to many cells in another column to see whether the employee is there "Present", missing "Absent", transferred "Per Diem", or remains blank. The problem I'm running in to is that I'm getting false "Absent" values for blanks from one month to the next. Cells that have no names in them are resulting in "Absent" when they should result in blanks.

Is there anyway to keep these false absences from showing up?

The formula I'm primarily using for this operation is:

=IF(AND(ISNA(MATCH(A22,$D$5:$D$92,0)),NOT(ISNA(MATCH(A22,$D$98:$D$116,0)))),"Per diem",IF(AND(NOT(ISNA(MATCH(A22,$D$5:$D$92,0))),ISNA(MATCH(A22,$D$98:$D$116,0))),"Present","Absent"))

To be honest, I'm not exactly sure how all the aspects of this formula work. There may be a better option in VBE to do this operation so I'm open to any suggestion.

Thanks for all your help.

I have made a few changes to your sample. The biggest is to wrap your formula in an IF() to get rid of the false "Absent" values.

I'm not sure about your criteria for determining the 3 possible results. For January you are checking for the presence of employees in February. For February you are checking within February and for March you are checking February and what will be April.

With that in mind I have changed the formulas in January and February to check for the following month.

The formulas you had in January were doing this:
If an employee name IS NOT present in rows 5 to 92 of col D (February) but IS present in rows 98 to 116 of col D then the result will be "Per Diem"
If the employee IS present in rows 5 to 92 of col D but IS NOT present in rows 98 to 116 of col D then the result will be "Present"
Otherwise you get "Absent"

Because you say you don't fully understand the formulas I have changed the ISNA(MATCH()) and NOT(ISNA(MATCH())) portions to simpler COUNTIF() formulas because I think they are easier to follow.

If those formulas are correct they can be copied over to col I for March but they will need April's data to function properly OR they can have another IF() to check for presence of next month data before calculating.

So, FWIW, here's an attempt to help.

Thank you so much Cutter!

I just applied the change in the formula to my main documents and it went off without a hitch.

I'm still unclear about how the countif function works in the formula. Does it search the criteria then return a 1 or a 0 (true or false, right?) then add in the appropriate values (i.e. absent, present, or perdiem)?

BTW. I felt like such a noob when you showed me how to remove the false absent values.

Thanks again for you patience and help!

EDIT: Countif can return a value of -1 too? is that why this part of the formula reads:

COUNTIF(S$107:S$125,P14)<>0),