+ Reply to Thread
Results 1 to 8 of 8

IF Formula returns #N/A even when criteria if criteria not met

  1. #1
    Registered User
    Join Date
    03-24-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    11

    IF Formula returns #N/A even when criteria if criteria not met

    I have set if all things fail/ FALSE, it should produce result "accessories" so why is it still showing #N/A? I'm yanking my hair out!
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: IF Formula returns #N/A even when criteria if criteria not met

    its probably one of the results set is not correct
    and IFERROR ( formula, "result if error") maybe the way to go

    i would test each one of your IF tests and see what happens
    you are not testing for the result to not return any result

    for example
    =VLOOKUP('Q1''13 Supporting'!$P19,Lists!$T$2:$U$29,2,FALSE)
    returns a #N/A
    so does
    =MATCH('Q1''13 Supporting'!$P19,Lists!$T$2:$T$29,0)
    and so thats not true or false

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: IF Formula returns #N/A even when criteria if criteria not met

    Not sure about which column formula you are talking about


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  4. #4
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    245

    Re: IF Formula returns #N/A even when criteria if criteria not met

    When the MATCH formula doesn't find a match, you get an error. Instead try:

    =IF($I21>25000,"Systems",IF('Q1''13 Supporting'!$P19="","Accessories",IFERROR(IF(MATCH('Q1''13 Supporting'!$P19,Lists!$T$2:$T$29,0),VLOOKUP('Q1''13 Supporting'!$P19,Lists!$T$2:$U$29,2,FALSE)),"Accessories")))
    <--- If you like the answer, press *.

  5. #5
    Registered User
    Join Date
    03-24-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: IF Formula returns #N/A even when criteria if criteria not met

    I was thinking that the formula is smart enough to know that if it doesn't match then it reflects "Accessories"
    However it returns N/A if there's no match.

  6. #6
    Registered User
    Join Date
    03-24-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: IF Formula returns #N/A even when criteria if criteria not met

    Thanks zumbalj ! It worked great and resolved the issue with the red cell! Cheers! You are a genius!

  7. #7
    Forum Contributor
    Join Date
    08-15-2012
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    245

    Re: IF Formula returns #N/A even when criteria if criteria not met

    Thanks! Glad to be of help Remember to mark your thread solved, and if you feel so inclined, press the * on the left side to add to my reputation.

  8. #8
    Registered User
    Join Date
    03-24-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: IF Formula returns #N/A even when criteria if criteria not met

    Hi zumbalj, I have clicked the *

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1