Forum Discussion

Victor Lapierre's avatar
Victor Lapierre
Copper Contributor
Sep 22, 2017

iferror error in formulator based of div0

I am using the following formula:

=IF($A19="","",($G19/VLOOKUP($A19,data.xlsx!A$3:E$5118,2,FALSE)))

 

I am getting the div0 error in some of the cells and do not want it showing for those cells. i tried

=iferror(IF($A19="","",($G19/VLOOKUP($A19,data.xlsx!A$3:E$5118,2,FALSE,0))))

 

But this just puts it into to many arguments error.

 

How do i keep my formula but have the cell return a 0 for cells that have the div0?

Formulas & Functions
Need Help
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Sep 22, 2017

    Victor,

     

    simply:

    =IFERROR(IF($A19="","",($G19/VLOOKUP($A19,data.xlsx!A$3:E$5118,2,FALSE))),0)
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Sep 22, 2017

    Victor, you added zero into wrong place, that's shall be second parameter for ISERROR, not the extra one for VLOOKUP

     

    =iferror(IF($A19="","",($G19/VLOOKUP($A19,data.xlsx!A$3:E$5118,2,FALSE))),0)

    simply comma will be enough if you'd like to return zero

    =iferror(IF($A19="","",($G19/VLOOKUP($A19,data.xlsx!A$3:E$5118,2,FALSE))),)

Resources