Many thanks for this Mariano.

I've tried it but currently get a #NA instead of the alternate value -
however, that's doubtless a syntax error on my part so I'll experiment!

Best wishes
SueJB

"Mariano" wrote:

try:
If(iserror(VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE)),[insert cell you
want to return if it does not find a result],VLOOKUP(A2,'Report
data'!$A$1:$P$9999,4,FALSE))

the cell or text placed in between the two vlookups in this formula is what
is displayed if the vlookup returns an error. So you could use the cell you
want to display or something like "Could not find."

Hope that helps!

"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