velohead66

When you do a vlookup, and the variable you are are looking for does not exist in the lookup area, then you get the #N/A code.
#N/A meaning value not available, because it is not there.

I know you can nest the 'vlookup' in an 'if' function, so that if true the vlookup happens, and if false 'zero' is returned.

Problem;
The 'if' statement uses something like 'iserror' or 'isnumber' as the condition to determine true/false.
I've tried but can not create the working formula, and time is against me.

Anyone know the correct formula ??

Just to summerise, I want a vlookup formula that returns the value if available, or zero if not available, instead of the #N/A if not available as at present.

Help !!

Martingb

Use the ISNA function:

e.g. = if(ISNA(vlookup(A1,Sheet2!A:B,2,false)),"MISSING", vlookup(A1,Sheet2!A:B,2,false))

This says that if the vlookup returns ISNA, the cell says "MISSING". You can replace this MISSING with different values.

See the help file for more on the IS functions.

Martin.