Solved #n/a if a cell is blank

Microsoft Excel 2010 - complete product...
May 9, 2012 at 02:52:36
Specs: windows 7, hp, pentium core i3
I have a spread sheet with two if functions. I want the cell that has the formulas to be blank if there is no information in either cell that the functions refer to. The result I am getting is an #n/a. here are my formulas =IF(E10>620,VLOOKUP(C10,Sheet2!G69:I79,3),IF(E10<621,VLOOKUP(C10,Sheet2!G79:I88,3))) the information pertains to door knob styles and corrisponding colors. Everything works fine as far as the values if a style is selected and a color selected, but this is also a printable order sheet, so I do not want #n/a to appear

See More: #n/a if a cell is blank

Report •


✔ Best Answer
May 9, 2012 at 07:38:47
You don't have the COUNTIF() function done correctly.

Try this and see how it works, I'm not 100% sure, as I have no way of testing,
as I don't know what your data looks like:

=IF(E10>620,IF(COUNTIF(Sheet2!G69:I79,C10),VLOOKUP(C10,Sheet2!G69:I79,3),""),IF(E10<621,COUNTIF(Sheet2!G79:I88,C10),VLOOKUP(C10,Sheet2!G79:I88,3)))

Notice that the COUNTIF() is checking the same things as your VLOOKUP()
So if the COUNTIF() returns TRUE, then your VLOOKUP() will not fail.

MIKE

http://www.skeptic.com/



#1
May 9, 2012 at 06:25:43
You could put a ISNA() check at the beginning
but that means you have to do the lookup twice, very ineffecent.

See here for a better way:

http://www.mrexcel.com/td0060.html

Google is your friend. :-)

MIKE

http://www.skeptic.com/


Report •

#2
May 9, 2012 at 06:56:43
I had tried the isna and iserror both, but like you said, duplicating the lookup is tough. I will try the countif and let you know. thanks

Report •

#3
May 9, 2012 at 07:06:34
=COUNTIF(C10,IF(E10>620,VLOOKUP(C10&E10,Sheet2!G69:I79,3),IF(E10<621,VLOOKUP(C10&E10,Sheet2!G79:I88,3)))) with this I can get rid of the NA, but then my values no longer work. I have been searching with google for week trying to find a solution. I don't think the countif will work because c10 is text, e10 is a numerical color code.

Report •

Related Solutions

#4
May 9, 2012 at 07:38:47
✔ Best Answer
You don't have the COUNTIF() function done correctly.

Try this and see how it works, I'm not 100% sure, as I have no way of testing,
as I don't know what your data looks like:

=IF(E10>620,IF(COUNTIF(Sheet2!G69:I79,C10),VLOOKUP(C10,Sheet2!G69:I79,3),""),IF(E10<621,COUNTIF(Sheet2!G79:I88,C10),VLOOKUP(C10,Sheet2!G79:I88,3)))

Notice that the COUNTIF() is checking the same things as your VLOOKUP()
So if the COUNTIF() returns TRUE, then your VLOOKUP() will not fail.

MIKE

http://www.skeptic.com/


Report •

#5
May 9, 2012 at 07:49:29
Your the man. That fixed it. I have only been working with excell for about two months, so everything I have been using thus far has been self learned via google. Starting to learn how to talk to excel a little better now. Thank you so much for your help.

Report •

#6
May 9, 2012 at 08:05:44
Just an observation:

You don't need to check E10 twice, once will do.

Your first check is to see if it is Greater Than 620, if it returns FALSE
then we already know it has to be Less Than 620 or Equal to 620
so why check to see if it is Less Than 620 when we already know it has to be,
just modify your first check to something like: E10>=620.

That changes your formula to something like:

=IF(E10>=620,IF(COUNTIF(Sheet2!G69:I79,C10),VLOOKUP(C10,Sheet2!G69:I79,3),""),IF(COUNTIF(Sheet2!G79:I88,C10),VLOOKUP(C10,Sheet2!G79:I88,3),""))

MIKE

http://www.skeptic.com/


Report •

#7
May 10, 2012 at 10:33:36
That makes perfect sense. I removed the second check and it still works just fine. I appreciate your help more than I can honestly express. It was something that probably could have been overlooked, but not very professional, and it bugged me.

Report •

Ask Question