Forum Discussion

bema2001's avatar
bema2001
Copper Contributor
Nov 05, 2025

Need help with a nested IF w/ISNA vlookup formula

A formula that I have been using for nearly 4 years has decided to stop working and I don't know how to fix it.  Everything I have tried fails miserably.

The formula is this:

=IF(OR($L5="Vacant",$L5="Unfunded",$L5="Intern"),$L5,IF(ISNA(VLOOKUP($M5,ActiveFTE[Employee ID],1,FALSE)),"Term","Yes"))

Where M5 is the employee ID number.  an the Active FTE is the current list of employees.  In the past if the person was no longer in the Actice list it would put Term and if it was there, Yes.

now, it returns #N/A - the ONLY thing that has changed from the last file and this file is the list of active employees.

if I remove the first IF/OR part of the formula it works beautifully, but I also need to flag vacant and unfunded positions.

I've tried ISNA,. IFNA, IFERROR as well as putting the IF/OR at the end instead of the beginning.

 

otherwise, any suggestion is greatly appreciated

 

cathy

 

 

excel
Formulas and Functions

4 Replies

  • JohnVergaraD's avatar
    JohnVergaraD
    Copper Contributor
    Nov 05, 2025

    Hi bema2001​ !

    Try this formula:

    =IF(OR($L5={"Vacant","Unfunded","Intern"}),$L5,
IF(COUNTIF(ActiveFTE[Employee ID],$M5),"Yes","Term"))

    You can shorten the formula with IFS like this:

    =IFS(OR($L5={"Vacant","Unfunded","Intern"}),$L5,
COUNTIF(ActiveFTE[Employee ID],$M5),"Yes",1,"Term")

    Or using SWITCH like this:

    =SWITCH(1,--OR($L5={"Vacant","Unfunded","Intern"}),$L5,
COUNTIF(ActiveFTE[Employee ID],$M5),"Yes","Term")

    You need to check the L5 value. If there's an error, the formula will also display an error. You can wrap this formula in an IFERROR function and assign a value to indicate when this occurs. Blessings!

  • mathetes's avatar
    mathetes
    Silver Contributor
    Nov 05, 2025

    If indeed it's the case that "the ONLY thing that has changed from the last file and this file is the list of active employees" then the problem isn't that the formula isn't working; it's something about or in that new list of active employees. That error means data is not available. (When one of my spreadsheets returns that error, when looking for things in a newly downloaded data file, I can always trace it back to some kind of change in the data itself.) 

    I would suggest leaving the formula alone and taking a close look at the data.

    • m_tarler's avatar
      m_tarler
      Bronze Contributor
      Nov 06, 2025

      also you say "if I remove the first IF/OR part of the formula it works beautifully," so i would check the value(s) for L5.  Maybe confirm the output of the following:

      =IF(OR($L5="Vacant",$L5="Unfunded",$L5="Intern"),$L5,"testing")

      =IF(ISNA(VLOOKUP($M5,ActiveFTE[Employee ID],1,FALSE)),"Term","Yes")

      based on your comment it sounds like you should get "testing" from the 1st and "Term" or "Yes" from the 2nd?

      Assuming you have a whole column with this formula do you get #N/A for all the lines or only some?

      • bema2001's avatar
        bema2001
        Copper Contributor
        Nov 06, 2025

        thanks for the suggestion....

        this morning i took the if/or part of the formula and put it on the end and it behaved like it did before - dunno why, but i'm just happy it worked😃

Resources