Taking out the DIV and N/A Errors in a Vlook up

exeldc

Board Regular
Joined
Sep 20, 2012
Messages
51
Hello Forum

Just took on a new workbook and noticed that the person before did not build their formulas for DIV and NA errors. :eek:

How can I adjust the following formula to have these areas turn to "0" instead of the errors. Thank you

=VLOOKUP($J$1,Today_FOOD,10,FALSE)/VLOOKUP($J$1,Today_FOOD,9,FALSE)
=(VLOOKUP($C8,Today_FOOD,37,FALSE))/(VLOOKUP($C8,Today_FOOD,36,FALSE))
=VLOOKUP($C15,Today_FOOD,17,FALSE)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

OFFICE360

New Member
Joined
Sep 26, 2014
Messages
9
Try using IFERROR or If(ISNA to handle the error.
Example=IFERROR(VLOOKUP($J$1,Today_FOOD,10,FALSE)/VLOOKUP($J$1,Today_FOOD,9,FALSE),0)
 

exeldc

Board Regular
Joined
Sep 20, 2012
Messages
51
Ok those worked!

What about =(VLOOKUP($J$1,Today_FOOD,37,FALSE)/VLOOKUP($J$1,Today_FOOD,36,FALSE))-(VLOOKUP($J$1,Yesterday_FOOD,37,FALSE)/VLOOKUP($J$1,Yesterday_FOOD,36,FALSE))
 

OFFICE360

New Member
Joined
Sep 26, 2014
Messages
9
I think you should add handlers for each of them or maybe change formula because any of those formula error will trigger IFERROR.

In short
=IFERROR(
(VLOOKUP($J$1,Today_FOOD,37,FALSE)/VLOOKUP($J$1,Today_FOOD,36,FALSE))-(VLOOKUP($J$1,Yesterday_FOOD,37,FALSE)/VLOOKUP($J$1,Yesterday_FOOD,36,FALSE)),"Error")

I cannot confirm the formula for you because my office is under re-installations...

 

exeldc

Board Regular
Joined
Sep 20, 2012
Messages
51

ADVERTISEMENT

Also I tried changing the formula below =(VLOOKUP($C8,Today_FOOD,37,FALSE))/(VLOOKUP($C8,Today_FOOD,36,FALSE))

to =IFERROR(VLOOKUP($C8,Today_FOOD,37,FALSE))/(VLOOKUP($C8,Today_FOOD,36,FALSE),0) but got a message saying Ive entered too few arguments
 

OFFICE360

New Member
Joined
Sep 26, 2014
Messages
9
check for missing brackets

=IFERROR((VLOOKUP($C8,Today_FOOD,37,FALSE))/(VLOOKUP($C8,Today_FOOD,36,FALSE)),0)
 

exeldc

Board Regular
Joined
Sep 20, 2012
Messages
51
Yeah also tried =IFERROR((VLOOKUP($J$1,Today_FOOD,37,FALSE)/VLOOKUP($J$1,Today_FOOD,36,FALSE))-(VLOOKUP($J$1,Yesterday_FOOD,37,FALSE)/VLOOKUP($J$1,Yesterday_FOOD,36,FALSE)),"Error") but got the error

 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,651
Messages
5,838,582
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top