Return alternate value if VLookup can't find match

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

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )



"SueJB" <[email protected]> wrote in message
news:[email protected]...
> 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

Hello Arvi

Many thanks for such a quick response.

I've just tried this but unfortunately it always returns the value from
column 2, even if there is a valid return from the VLookup.

Have I misunderstood something?

Best wishes
Sue

"Arvi Laanemets" wrote:

> Hi
>
> =IF(ISERROR(VLOOKUP(A3,'Report
> data'!$A$1:$P$9999,4,FALSE)),B3,VLOOKUP(A3,'Report
> data'!$A$1:$P$9999,4,FALSE))
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
>
> "SueJB" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >