MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.