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

exeldc

Board Regular
Hello Forum

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

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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

OFFICE360

New Member
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)

Ok those worked!

OFFICE360

New Member
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

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
check for missing brackets

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

exeldc

Board Regular
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

Replies
9
Views
151
Replies
3
Views
48
Replies
2
Views
59
Replies
3
Views
305
Replies
1
Views
52

1,109,445
Messages
5,528,801
Members
409,835
Latest member
Mafu1267

This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...