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 :)

✔ 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_trueandvalue_if_falsefor every IF.

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

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

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 NADo you mean the Excel Error #NA ?

MIKE

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

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.

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_trueandvalue_if_falsefor every IF.

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

Thank you so much!!! It worked! :) I felt like I was trying everything, but I didn't even think about doing IF(NOT(ISNA.

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.

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_falseargument to return.In other words, it reached a point where it was looking for a

value_if_falseto 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.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History