Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Do not want #N/A showing in cells (xp & 2003)

    In the attached workbook, I have cells in columns i,j,s,t,ac,ad and rows 24,25,26 with '#N/A" in them caused by some sophisticated formula. The formula contains a ISNA phrase in it, but still hase the "#N/A" result. How do I get rid of them and maintain the formula.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Do not want #N/A showing in cells (xp & 2003)

    You test if the "outer" VLOOKUP results in #N/A. If so, your formula results in another VLOOKUP, and you don't test that. Try this monster in I24:

    =IF(ISNA(VLOOKUP($E24&$L$8,'HR DELTA'!$K$3:$T$45,3,FALSE)),IF(ISNA(VLOOKUP($E24,' BEG SALARY'!$B$3:$I$31,7,FALSE)),"",VLOOKUP($E24,'BEG SALARY'!$B$3:$I$31,7,FALSE)),VLOOKUP($E24&$L$8,'HR DELTA'!$K$3:$T$45,3,FALSE))

    It'll probably be split into two or more lines in this post, but it is one formula.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Do not want #N/A showing in cells (xp & 2003)

    Ok, I feel like I'm in a endless labrynth. If you have the time Hans, would you please explain each of the parts of this Beheemeth Formula. I'm not sure what each part does any more.

    Thanks,

    It works grand!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Do not want #N/A showing in cells (xp & 2003)

    Let's abbreviate

    VLOOKUP($E24&$L$8,'HR DELTA'!$K$3:$T$45,3,FALSE)

    with P, and

    VLOOKUP($E24,'BEG SALARY'!$B$3:$I$31,7,FALSE)

    with Q. The formula then becomes

    =IF(ISNA(P),IF(ISNA(Q),"",Q),P)

    This says:

    If P results in #N/A, then
    If Q also results in #N/A, then
    Return an empty string ""
    Else (Q is not #N/A)
    Return the result of Q
    Else (P is not #N/A)
    Return the result of P

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •