If/lookup functions

Posted by Jeannette on December 03, 2001 8:12 AM

I have to create a order form, and I have done so using a lookup table to enter some of the information. But the cell displays #N/A and I was trying to hide it using a IF function. I have managed to hide it but I cant get the IF function to go on to use the lookup table as well. Does anyone have any suggestions?

Posted by Aladin Akyurek on December 03, 2001 8:20 AM

One pretty efficient way of avoiding #N/A is:

=IF(COUNTIF(A1:A10,lookup-value),VLOOKUP(lookup-value,$A$1:$C$10,2,match-type),"")

This formula is referring to a 3-column table: the important thing is that COUNTIF must refer to the first column of the lookup table.

Aladin

============

Posted by Katt on December 03, 2001 10:06 AM

You can use
=if(iserror(vlookup(xx,xxx,x,false)),0,vlookup(xx,xxx,x,false))
This will replace your N/A with a zero, if you don't not want a zero, you can also use " ", or whatever else you want.
Katt