Vlookup returning error message

[eefs]

I'm using the Vlookup effectively to find the data I need from an array. range_lookup is False because I want exact matches only. If an exact match is not found, then #N/A is returned. I need to sum the values found, but can't do this if there are #N/A's peppered in my results. Is there a way of either getting the vlookup function to return a blank or 0 if no exact match is found, or alternatively is there a way of substituing the #N/A with a blank or 0 once it's appeared in the results section. nothing I've tried seems to work yet.

[Iain Lewis]

Try this :

IF(ISERROR(VLOOKUPA1,nameoflookup,2,FALSE)),"0.00",VLOOKUP(A1,nameoflookup,2,FALSE))

- I've aded the missing "(" back in

[ This Message was edited by: Iain Lewis on 2002-05-16 03:54 ]

[BarrettM]

The code below returned N/A when it did not find a match, however with the additions shown it returns "Non existent code" when the value is N/a
VLOOKUP(C4,RollupCode,2,FALSE)


IF(ISNA(VLOOKUP(C4,RollupCode,2,FALSE)), "Non Existent Code",VLOOKUP(C4,RollupCode,2,FALSE))

I hope this helps you

[Nibbles]

Hi,

Try:

IF(ISNA(VLOOKUP(C1,$A$1:$B$14,2,0)),0,VLOOKUP(C1,$A$1:$B$14,2,0))

Replace the 0 with "" if you would rather have a blank cell reported, and obviously change cell refs to suit.

Nibbs

[ This Message was edited by: Nibbles on 2002-05-16 03:54 ]

[eefs]

Thank you so much, they both worked perfectly (i tried them both for my own experience)