If the vlookup is returning a 0, from my experience that means it is finding
the lookup value, but the data that it is returning is blank (or a zero.)
Therefore, you could change your formula to check for a 0 like so:
=if(VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE) = 0, "what you want
here", VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE))
Then instead of "what you want here", you could make it another vlookup (for
instance you mentioned something about column 2 I believe, so maybe
vlookup(a2, $a$1:$p$9999,2,false)

"SueJB" wrote:

Hi Niek

No, it's not part of a more complex formula.

I have :

=VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE)

This is in column E of the Staff worksheet. It use a unique ID in column A
of Staff to look up a name in column D (4th column) of Report Data. If the
name is present, it enters it in column E of Staff. It not, it enters a zero
- but I haven't done anything to get zero rather than #NA - as far as I know.

Sorry to be so ill-informed. :-(

Sue

"Niek Otten" wrote:

Hi Sue JB,

I think the information you gave us is not complete. If the 4th argument of
the VLOOKUP is FALSE and the item can not be found in the table, a #NA is
returned, not a zero.
Is your formula part of a more complex formula in which you intercept the
#NA?

--
Kind regards,

Niek Otten

"SueJB" wrote in message
...
Hi all

I'm finding my way round the intricacies of VLookup and making some
progress, but am stumped by a small problem.

I have a working VLookup in a worksheet called Staff

=VLOOKUP(A3,'Report data'!$A$1:$P$9999,4,FALSE)

which returns a name from the Report Data sheet if it's present, and a 0
if
not. However, rather than the zero I would like it to return the value of
column 2 in the Staff (active) worksheet.

Can this be done with a nested function or does it need VBA?

All comments gratefully received!

Sue JB