Need Help to remove #N/A from a cell

[nick2009]

Hi all
I have formula
=IF(T19="Labour",VLOOKUP(Q19,Labour!$D$9:$K$84,8,FALSE),IF(T19="Plant",VLOOKUP(Q19,Plant!$D$14:$I$156,6,FALSE),IF(T19="Materials",VLOOKUP(Q19,Materials!$D$3:$O$86,11,FALSE),IF(T19="Subcontractor",VLOOKUP(Q19,Subcontractors!$D$3:$J$94,7,FALSE),"0"))))

It is looking up rates in various sheets based on the item from a drop down in cell Q19. If Q19 is blank or not an item in the drop down list I get a #N/A in the cell where the rate should appear.

How can I stop the #N/A?

[Limor_OP]

Add iferror maybe:

Please Login or Register  to view this content.

[AliGW]

Or:

=IFNA(IF(T19="Labour",VLOOKUP(Q19,Labour!$D$9:$K$84,8,FALSE),IF(T19="Plant",VLOOKUP(Q19,Plant!$D$14:$I$156,6,FALSE),IF(T19="Materials",VLOOKUP(Q19,Materials!$D$3:$O$86,11,FALSE),IF(T19="Subcontractor",VLOOKUP(Q19,Subcontractors!$D$3:$J$94,7,FALSE),"0")))),"")

[arthurbr]

Be aware that the IFERROR function should be used with caution as it hides ALL errors, not only N/A...
That why Ali very wisely avoided it

[AliGW]

Easy to say, however IFNA is not available in some earlier versions. YOu'd have to use:

=IF(ISNA(formula),"",formula)

[nick2009]

Thanks everyone for your help.
Now on to the next issue.... watch this space!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[nick2009]

Thanks Ali

I need a little more help with this!
In the "" at the end of the formula I have input the word "error" but is it possible that if Q19 is blank to just put a zero in the cell?

[AliGW]

Yes!

Change "" to 0.

[nick2009]

I am looking for two options, because Q19 is a drop down list if something that is not on the list is entered it will show an error from the "error" but if its left blank it still shows Error. If its blank an it remain zero?
Sorry for being a pain!!!

[AliGW]

This?


=IF(Q19="",0,IFNA(IF(T19="Labour",VLOOKUP(Q19,Labour!$D$9:$K$84,8,FALSE),IF(T19="Plant",VLOOKUP(Q19,Plant!$D$14:$I$156,6,FALSE),IF(T19="Materials",VLOOKUP(Q19,Materials!$D$3:$O$86,11,FALSE),IF(T19="Subcontractor",VLOOKUP(Q19,Subcontractors!$D$3:$J$94,7,FALSE),"0")))),""))