Forum Discussion

Pamr0703's avatar
Pamr0703
Copper Contributor
Feb 04, 2020

Need help getting rid of #N/A in my formula; please help

'=IF($AB$4='8B-Saginaw'!$C$9,VLOOKUP('Adjusted Inventory Summary'!F7,'8B-Saginaw'!$A:$F,6,0)). I need to return a zero or blank.

 

excel

14 Replies

  • Charla74's avatar
    Charla74
    Iron Contributor
    Feb 04, 2020
    You can wrap your formula in an IFERROR function as follows:

    =IFERROR(IF($AB$4='8B-Saginaw'!$C$9,VLOOKUP('Adjusted Inventory Summary'!F7,'8B-Saginaw'!$A:$F,6,0)),"") - The "" at the end returns a blank cell if the formula results in an error; you can change this to 0 (or whatever you like, in fact) if you prefer a different result.
      • Charla74's avatar
        Charla74
        Iron Contributor
        Feb 04, 2020

        Pamr0703 

         

        Very welcome - If you do decide to use the IFERROR option, you can find a nice little macro at the below link, which will let you select the whole range of formulae and wrap them all in one go (you will need to delete the n/a in the code though, to return a blank cell)

         

        https://excelhelphq.com/how-to-apply-iferror-on-many-cells-automatically-using-excel-vba-macro/

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Feb 04, 2020

    Hello Pamr0703,

     

    Return 0 if false:

    =IF($AB$4='8B-Saginaw'!$C$9,VLOOKUP('Adjusted Inventory Summary'!F7,'8B-Saginaw'!$A:$F,6,0),0)

     Return blank if false:

    =IF($AB$4='8B-Saginaw'!$C$9,VLOOKUP('Adjusted Inventory Summary'!F7,'8B-Saginaw'!$A:$F,6,0),"")

     

      • PReagan's avatar
        PReagan
        Bronze Contributor
        Feb 04, 2020

        Pamr0703 

         

        The #N/A error suggests that the lookup_value is not located in the table_array. However, without a sample file, it would be impossible to confirm this suspicion.