Solved ISNA in IF FUNCTION

September 13, 2011 at 08:46:03
Specs: Windows 7
I current have an equation that is formatted as:

=IF(VLOOKUP(B26,Sheet2!A3:G25,6,FALSE)>0,Sheet1!T26+VLOOKUP(Sheet1!B26,Sheet2!A3:G25,6,FALSE),IF(AND(Q26+R26=0),0,IF(OR(R26>Q26,R26<0),T26*(1+(VLOOKUP(B26,Sheet2!$A$3:$E$25,5,FALSE))),Sheet1!T26*(1+Sheet1!$U$3))))

The VLOOKUP is looking up a part number in another worksheet. This equation works, but I need it to not return NA when the part number is not listed in the other worksheet. I am wondering how to put this into the equation. Any help would be greatly appreciated! Thanks :)


See More: ISNA in IF FUNCTION

Report •


✔ Best Answer
September 20, 2011 at 16:47:12
It sounds like you need something of this format:

=IF(NOT(ISNA(VLOOKUP(whatever))), VLOOKUP, (whatever), IF(AND(Q25=0,R25=0),G25, etc.

Basically what this says is:

If VLOOKUP(whatever) does NOT return #N/A then evaluate VLOOKUP(whatever). If it does, then process the other IF statements.

Getting the parentheses in the right places is usually the tricky part. It's all about making sure you keep track of your value_if_true and value_if_false for every IF.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
September 13, 2011 at 12:31:17
Here are some ways around the problem of #N/A:

http://www.ozgrid.com/forum/showthr...

MIKE

http://www.skeptic.com/


Report •

#2
September 14, 2011 at 08:43:00
I need it to still do the second if function in my equation if it is NA, but when I cannot figure out how to make the equation

Report •

#3
September 14, 2011 at 10:24:36
Sorry, you've got me confused.

You have a very complicated formula, doing several things using at least two separate sheets and doing several lookups.

Without knowing how and what your doing the best I can offer is to simplify your operations.

Take each part that needs to be accomplished and put the results in it's own cell and build up from there.

Try using a =AND() - =OR() function so the second IF get run regardless.

I also do not understand this section of formula: IF(AND(Q26+R26=0)

Why is there an =AND() function and what is it supposed to do?

I need it to still do the second if function in my equation if it is NA

Do you mean the Excel Error #NA ?
MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 14, 2011 at 11:12:07
I just found your other post.
Please don't post a whole new thread when you already have one started on the same subject.

You could try reversing your logic:

In stead of: =IF(VLOOKUP(B26,Sheet2!A3:G25,6,FALSE)>0,

Try: =IF(VLOOKUP(B26,Sheet2!A3:G25,6,FALSE)<=0,

Then reverse the rest of your formula.

MIKE

http://www.skeptic.com/


Report •

#5
September 20, 2011 at 12:59:58
The problem is with the #NA. Basically, I am wondering how to make the cell insert what's in the vlookup if it finds the part number, but if the part number is not in the sheet, I need it to do the next if statements (instead of just putting #NA).
So far I have:
=IF(VLOOKUP(B25,Sheet2!A3:G10,7,FALSE)>0,VLOOKUP(B25,Sheet2!A3:G10,7,FALSE),IF(AND(Q25=0,R25=0),G25,IF(R25>Q25,G25*(1-R25),IF(Q25>R25,G25*(1-Q25)))))

Somewhere in this formula I need to tell it to not put NA#, and instead do the next if statements.


Report •

#6
September 20, 2011 at 16:47:12
✔ Best Answer
It sounds like you need something of this format:

=IF(NOT(ISNA(VLOOKUP(whatever))), VLOOKUP, (whatever), IF(AND(Q25=0,R25=0),G25, etc.

Basically what this says is:

If VLOOKUP(whatever) does NOT return #N/A then evaluate VLOOKUP(whatever). If it does, then process the other IF statements.

Getting the parentheses in the right places is usually the tricky part. It's all about making sure you keep track of your value_if_true and value_if_false for every IF.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
September 21, 2011 at 08:52:43
Thank you so much!!! It worked! :) I felt like I was trying everything, but I didn't even think about doing IF(NOT(ISNA.

Report •

#8
September 21, 2011 at 09:12:12
I have one more question!

To make it work right now I had to add

=IF(NOT(ISNA(VLOOKUP(B25,Sheet2!H3:N25,7,FALSE))),VLOOKUP(B25,Sheet2!H3:N25,7,FALSE),IF(AND(AE25=0,AF25=0),G25,IF(AF25>AE25,G25*(1-AF25),G25*(1-AE25))))

The problem with this, is that I need it to list the VLOOKUP price only when it is greater than 0. I tried to do this equation for this:

=IF(NOT(ISNA(VLOOKUP(B25,Sheet2!H3:N25,7,FALSE))),IF(VLOOKUP(B25,Sheet2!H3:N25,7,FALSE)>0,VLOOKUP(B25,Sheet2!H3:N25,7,FALSE),IF(AND(AE25=0,AF25=0),G25,IF(AF25>AE25,G25*(1-AF25),G25*(1-AE25)))))

The problem is that it returns "false" instead of moving on to checking the next if statements.


Report •

#9
September 21, 2011 at 12:04:42
Since your formula involves so many cells and multiple sheets, there's no way I'm going to set up a workbook to test it. The best I can do is offer suggestions...

Any IF statement that returns FALSE (when you didn't specifically ask it to) is returning the default answer when there is no value_if_false argument to return.

In other words, it reached a point where it was looking for a value_if_false to return and didn't find one.

You could try to single step through the formula using Tools...Formula Auditing...Evaluate Formula to see where is strays off the path you want it to follow.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question