vlookup
Posted by Greg Taylor on November 07, 2001 8:10 AM
When a vlookup request returns an "#N/A" in an array because no exact match was found, the sum function for that row or column in the array also reverts to a result of "#N/A". This is a maddening reality that I have not been able to work around. What can I do????
Posted by Aladin Akyurek on November 07, 2001 8:18 AM
Two things:
(1) Keep #N/A's and use SUMIF instead of SUM as in:
=SUMIF(A1:A10,"<>#N/A")
(2) Control VLOOKUP so that it doesn't return #N/A when the lookup value is not listed in the lookup table. If you'd want to apply this option, use:
=IF(COUNTIF(B1:B25,lookup-value),VLOOKUP(lookup-value,B1:D25,2,0)
Aladin
Posted by Aladin Akyurek on November 07, 2001 8:46 AM
=IF(COUNTIF(B1:B25,lookup-value),VLOOKUP(lookup-value,B1:D25,2,0),"")
to make the formula to return "" (a blank).
==========
Posted by Greg Taylor on November 07, 2001 9:43 AM
Aladin, you're the best! Thanks for your quick reply. I am new to the message board but I plan to get involved, for sure. Later.
Posted by Hodge on November 07, 2001 10:37 AM
I had a similar problem and used:
IF(ISERROR(VLOOKUP($A4,Monday!$A$1:$H$499,8,FALSE)),"0",VLOOKUP($A4,Monday!$A$1:$H$499,8,FALSE))
Gets rid of all the #N/As no matter what.
