Microsoft Excel 2010 - complete product...

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

✔ 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

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

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

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

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

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.

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

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.

Ask Your Question

Weekly Poll