Problem with nested If OR functions

[Christopher135]

Hi guys,

hopefully a simple one for you. (Oh if only they were all simple!)

Please Login or Register  to view this content.

.

This formula is entered into *E13* and from my understanding, this formula should do something like this:

Check E9, is it a match for E12?
If yes, Enter OIC in E13
If no, run nested IF/OR Function

Check E12, is it a match for any value in defined name Leave_Types?
If yes, duplicate data found in E12 into cell E13
If no, leave cell E13 blank

The problem? It always leaves E13 blank, unless the first value is True, in which case it enters OIC.

Help please?

[Christopher135]

So I've figured out the problem, but don't know the solution.

Its only checking the row in the Defined Name Leave_Types as the formula (i.e. row13). It doesn't find a match, and so enters blank data.

How do I make it check the entire defined name list?

[benishiryo]

hi there again, Christopher. it looks like you don't need OR. according to your assumptions, maybe:
=IF(E$9=E12,"OIC",IF(COUNTIF(Leave_Types,E$12),E12,""))

[Christopher135]

Ah Benishiryo. If I was in charge of HR at my organisation, we'd be putting you on retainer as Excel Consultant. So much help from one person. Thank you so much

[benishiryo]

*grins*
if there's ever a day, i'm counting on you then. you're very welcome