Match Formula to Return 1 of 3 Possible Values

Originally Posted by JBeaucaire

Maybe:

=IF(MATCH(D7,$G$7:$G$87,0), IF(MATCH(D7,G145:G157,0), "<--Per Diem", "<--present"), "<--Absent"))

Thank you for your reply! Unfortunately this returns "<--Per Diem" and nothing else.

I've attached an example of the workbook; please let me know if you need further clarification.

Thanks for the help!

Maybe:

Please Login or Register  to view this content.

ugly, but seems to work (if I understand your question)

Originally Posted by Cutter

Maybe:

Please Login or Register  to view this content.

ugly, but seems to work (if I understand your question)

Off the bat the formula isn't working, however, I never thought to use "ISNA". That might work if I apply it correctly. Let me take a bash at it and see if I can make it work.

It's giving the following results for me:

If employee is present in January, and present in February - Present

If employee is present in January, not present in February - Absent

If employee not present in January, present in February = Per Diem

If employee not present in January or February - #N/A


I assumed the upper region is January, lower region is February in column G

Originally Posted by Cutter

It's giving the following results for me:

If employee is present in January, and present in February - Present

If employee is present in January, not present in February - Absent

If employee not present in January, present in February = Per Diem

If employee not present in January or February - #N/A


I assumed the upper region is January, lower region is February in column G

I'm sorry, in the worksheet each month has it's own column. I monkeyed with the formula and arrived at:

=IF(AND(ISNA(MATCH(A10,$D$7:$D$77,0)),NOT(ISNA(MATCH(A10,D88:D106,0)))),"<--per diem",IF(AND(MATCH(A10,D10:D80,0)>0,ISNA(MATCH(A10,D10:D80,0))),"<--Absent","<--present"))

However, terminated employees are showing as #N/A instead of "<--Absent" I think I might have moved some of the lookup_arrays to the wrong places.

The last part of your formula has the same ranges being searched for a match.

Perhaps I should change the Formula to:

=IF(AND(ISNA(MATCH(A10,$D$7:$D$77,0)),NOT(ISNA(MATCH(A10,$D$85:$D$103,0)))),"<--per diem",IF(AND(MATCH(A10,$D$7:$D$77,0)>0,ISNA(MATCH(A10,$D$85:$D$103,0))),"<--Present","<--Absent"))

But this iteration is returning #NA and not "<--Absent" on terminated employees.

You reversed the order of the last two results.
You have Present before Absent

Use:

Please Login or Register  to view this content.

Just to make it consistently ugly (but maybe easier to follow the logic) you can use this:

Please Login or Register  to view this content.

It only returns #N/A if no match in either range - you should (because I do) get one of the three choices by the other 3 scenarios.

Thank You for the support Cutter!

I only care about the formulas in Column C because the workbook is for a whole year. The formulas in Column F are nothing but an example of my total workbook and will be changed to reflect C when I re-apply it to my original.

I really can't adequately express my gratitude for you support. Thank you, again.