Excel Formula with IF ISNA and VLOOKUP

Microsoft Microsoft office excel 2007 -...
May 7, 2010 at 14:09:34
Specs: Windows XP
I have the following IF excel formula:

=IF(ISNA(VLOOKUP(M3,com,23,0))=TRUE,VLOOKUP(E3,ach,6,0),VLOOKUP(M3,com,23,0))

The formula works fine but if the second part of the vlookup is NA, I want it to return a blank rather than NA. Can someone tell me how to incorporate that into the formula?

Thanks!


See More: Excel Formula with IF ISNA and VLOOKUP

Report •

#1
May 7, 2010 at 14:43:20
The ISNA function does not have to be "checked" to see if it is TRUE or not.

It will evaluate to either TRUE of FALSE and the IF statement will proceed based on that value.

Is this what you want?

=IF(ISNA(VLOOKUP(M3,com,23,0)),
IF(ISNA(VLOOKUP(E3,ach,6,0)),"",
VLOOKUP(E3,ach,6,0)),
VLOOKUP(M3,com,23,0))

If M3 isn't found in the first column of com and E3 is not found in the first column of ach, return a blank cell.

If M3 is found in the first column of com, return the value from column 23 of com.

If M3 isn't found in the first column of com but E3 is found in the first column of ach, the return then value in column 6 of ach.


Report •

#2
May 7, 2010 at 16:10:55
I tried plugging that into my spreadsheet but unfortunately, that formula doesn't work for what I want. My formula works and does what I want it to do but I want to tweak it so that when it looks at my second vlookup, I want it to return a blank rather than the NA if there isn't a value there. Hope that makes sense. Thanks


Report •

#3
May 7, 2010 at 19:22:53
First your say: My formula works and does what I want it to do

Then you say: I want it to return a blank rather than the NA if there isn't a value there.

Therefore, it doesn't do what you want it to do.

In my post, I explained what my formula will do. If that is not what you want, please explain, step by step, just like I did, what you want to happen.


Report •
Related Solutions


Ask Question