software

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.laanemetslt;atgt;tarkon.ee )
quot;SueJBquot; gt; wrote in message
...
gt; Hi all
gt;
gt; I'm finding my way round the intricacies of VLookup and making some
gt; progress, but am stumped by a small problem.
gt;
gt; I have a working VLookup in a worksheet called Staff
gt;
gt; =VLOOKUP(A3,'Report data'!$A$1:$P$9999,4,FALSE)
gt;
gt; which returns a name from the Report Data sheet if it's present, and a 0
gt; if
gt; not. However, rather than the zero I would like it to return the value of
gt; column 2 in the Staff (active) worksheet.
gt;
gt; Can this be done with a nested function or does it need VBA?
gt;
gt; All comments gratefully received!
gt;
gt; 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

quot;Arvi Laanemetsquot; wrote:

gt; Hi
gt;
gt; =IF(ISERROR(VLOOKUP(A3,'Report
gt; data'!$A$1:$P$9999,4,FALSE)),B3,VLOOKUP(A3,'Report
gt; data'!$A$1:$P$9999,4,FALSE))
gt;
gt;
gt; --
gt; Arvi Laanemets
gt; ( My real mail address: arvi.laanemetslt;atgt;tarkon.ee )
gt;
gt;
gt;
gt; quot;SueJBquot; gt; wrote in message
gt; ...
gt; gt; Hi all
gt; gt;
gt; gt; I'm finding my way round the intricacies of VLookup and making some
gt; gt; progress, but am stumped by a small problem.
gt; gt;
gt; gt; I have a working VLookup in a worksheet called Staff
gt; gt;
gt; gt; =VLOOKUP(A3,'Report data'!$A$1:$P$9999,4,FALSE)
gt; gt;
gt; gt; which returns a name from the Report Data sheet if it's present, and a 0
gt; gt; if
gt; gt; not. However, rather than the zero I would like it to return the value of
gt; gt; column 2 in the Staff (active) worksheet.
gt; gt;
gt; gt; Can this be done with a nested function or does it need VBA?
gt; gt;
gt; gt; All comments gratefully received!
gt; gt;
gt; gt; Sue JB
gt;
gt;
gt;

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

quot;SueJBquot; gt; wrote in message
...
gt; Hi all
gt;
gt; I'm finding my way round the intricacies of VLookup and making some
gt; progress, but am stumped by a small problem.
gt;
gt; I have a working VLookup in a worksheet called Staff
gt;
gt; =VLOOKUP(A3,'Report data'!$A$1:$P$9999,4,FALSE)
gt;
gt; which returns a name from the Report Data sheet if it's present, and a 0
gt; if
gt; not. However, rather than the zero I would like it to return the value of
gt; column 2 in the Staff (active) worksheet.
gt;
gt; Can this be done with a nested function or does it need VBA?
gt;
gt; All comments gratefully received!
gt;
gt; 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

quot;Niek Ottenquot; wrote:

gt; Hi Sue JB,
gt;
gt; I think the information you gave us is not complete. If the 4th argument of
gt; the VLOOKUP is FALSE and the item can not be found in the table, a #NA is
gt; returned, not a zero.
gt; Is your formula part of a more complex formula in which you intercept the
gt; #NA?
gt;
gt; --
gt; Kind regards,
gt;
gt; Niek Otten
gt;
gt; quot;SueJBquot; gt; wrote in message
gt; ...
gt; gt; Hi all
gt; gt;
gt; gt; I'm finding my way round the intricacies of VLookup and making some
gt; gt; progress, but am stumped by a small problem.
gt; gt;
gt; gt; I have a working VLookup in a worksheet called Staff
gt; gt;
gt; gt; =VLOOKUP(A3,'Report data'!$A$1:$P$9999,4,FALSE)
gt; gt;
gt; gt; which returns a name from the Report Data sheet if it's present, and a 0
gt; gt; if
gt; gt; not. However, rather than the zero I would like it to return the value of
gt; gt; column 2 in the Staff (active) worksheet.
gt; gt;
gt; gt; Can this be done with a nested function or does it need VBA?
gt; gt;
gt; gt; All comments gratefully received!
gt; gt;
gt; gt; Sue JB
gt;
gt;
gt;

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 quot;Could not find.quot;

Hope that helps!

quot;SueJBquot; wrote:

gt; Hi Niek
gt;
gt; No, it's not part of a more complex formula.
gt;
gt; I have :
gt;
gt; =VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE)
gt;
gt; This is in column E of the Staff worksheet. It use a unique ID in column A
gt; of Staff to look up a name in column D (4th column) of Report Data. If the
gt; name is present, it enters it in column E of Staff. It not, it enters a zero
gt; - but I haven't done anything to get zero rather than #NA - as far as I know.
gt;
gt; Sorry to be so ill-informed. :-(
gt;
gt; Sue
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt; gt; Hi Sue JB,
gt; gt;
gt; gt; I think the information you gave us is not complete. If the 4th argument of
gt; gt; the VLOOKUP is FALSE and the item can not be found in the table, a #NA is
gt; gt; returned, not a zero.
gt; gt; Is your formula part of a more complex formula in which you intercept the
gt; gt; #NA?
gt; gt;
gt; gt; --
gt; gt; Kind regards,
gt; gt;
gt; gt; Niek Otten
gt; gt;
gt; gt; quot;SueJBquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi all
gt; gt; gt;
gt; gt; gt; I'm finding my way round the intricacies of VLookup and making some
gt; gt; gt; progress, but am stumped by a small problem.
gt; gt; gt;
gt; gt; gt; I have a working VLookup in a worksheet called Staff
gt; gt; gt;
gt; gt; gt; =VLOOKUP(A3,'Report data'!$A$1:$P$9999,4,FALSE)
gt; gt; gt;
gt; gt; gt; which returns a name from the Report Data sheet if it's present, and a 0
gt; gt; gt; if
gt; gt; gt; not. However, rather than the zero I would like it to return the value of
gt; gt; gt; column 2 in the Staff (active) worksheet.
gt; gt; gt;
gt; gt; gt; Can this be done with a nested function or does it need VBA?
gt; gt; gt;
gt; gt; gt; All comments gratefully received!
gt; gt; gt;
gt; gt; gt; Sue JB
gt; gt;
gt; gt;
gt; gt;

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, quot;what you want
herequot;, VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE))
Then instead of quot;what you want herequot;, 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)

quot;SueJBquot; wrote:

gt; Hi Niek
gt;
gt; No, it's not part of a more complex formula.
gt;
gt; I have :
gt;
gt; =VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE)
gt;
gt; This is in column E of the Staff worksheet. It use a unique ID in column A
gt; of Staff to look up a name in column D (4th column) of Report Data. If the
gt; name is present, it enters it in column E of Staff. It not, it enters a zero
gt; - but I haven't done anything to get zero rather than #NA - as far as I know.
gt;
gt; Sorry to be so ill-informed. :-(
gt;
gt; Sue
gt;
gt; quot;Niek Ottenquot; wrote:
gt;
gt; gt; Hi Sue JB,
gt; gt;
gt; gt; I think the information you gave us is not complete. If the 4th argument of
gt; gt; the VLOOKUP is FALSE and the item can not be found in the table, a #NA is
gt; gt; returned, not a zero.
gt; gt; Is your formula part of a more complex formula in which you intercept the
gt; gt; #NA?
gt; gt;
gt; gt; --
gt; gt; Kind regards,
gt; gt;
gt; gt; Niek Otten
gt; gt;
gt; gt; quot;SueJBquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Hi all
gt; gt; gt;
gt; gt; gt; I'm finding my way round the intricacies of VLookup and making some
gt; gt; gt; progress, but am stumped by a small problem.
gt; gt; gt;
gt; gt; gt; I have a working VLookup in a worksheet called Staff
gt; gt; gt;
gt; gt; gt; =VLOOKUP(A3,'Report data'!$A$1:$P$9999,4,FALSE)
gt; gt; gt;
gt; gt; gt; which returns a name from the Report Data sheet if it's present, and a 0
gt; gt; gt; if
gt; gt; gt; not. However, rather than the zero I would like it to return the value of
gt; gt; gt; column 2 in the Staff (active) worksheet.
gt; gt; gt;
gt; gt; gt; Can this be done with a nested function or does it need VBA?
gt; gt; gt;
gt; gt; gt; All comments gratefully received!
gt; gt; gt;
gt; gt; gt; Sue JB
gt; gt;
gt; gt;
gt; gt;

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

quot;Marianoquot; wrote:

gt; try:
gt; If(iserror(VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE)),[insert cell you
gt; want to return if it does not find a result],VLOOKUP(A2,'Report
gt; data'!$A$1:$P$9999,4,FALSE))
gt;
gt; the cell or text placed in between the two vlookups in this formula is what
gt; is displayed if the vlookup returns an error. So you could use the cell you
gt; want to display or something like quot;Could not find.quot;
gt;
gt; Hope that helps!
gt;
gt; quot;SueJBquot; wrote:
gt;
gt; gt; Hi Niek
gt; gt;
gt; gt; No, it's not part of a more complex formula.
gt; gt;
gt; gt; I have :
gt; gt;
gt; gt; =VLOOKUP(A2,'Report data'!$A$1:$P$9999,4,FALSE)
gt; gt;
gt; gt; This is in column E of the Staff worksheet. It use a unique ID in column A
gt; gt; of Staff to look up a name in column D (4th column) of Report Data. If the
gt; gt; name is present, it enters it in column E of Staff. It not, it enters a zero
gt; gt; - but I haven't done anything to get zero rather than #NA - as far as I know.
gt; gt;
gt; gt; Sorry to be so ill-informed. :-(
gt; gt;
gt; gt; Sue
gt; gt;
gt; gt; quot;Niek Ottenquot; wrote:
gt; gt;
gt; gt; gt; Hi Sue JB,
gt; gt; gt;
gt; gt; gt; I think the information you gave us is not complete. If the 4th argument of
gt; gt; gt; the VLOOKUP is FALSE and the item can not be found in the table, a #NA is
gt; gt; gt; returned, not a zero.
gt; gt; gt; Is your formula part of a more complex formula in which you intercept the
gt; gt; gt; #NA?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Kind regards,
gt; gt; gt;
gt; gt; gt; Niek Otten
gt; gt; gt;
gt; gt; gt; quot;SueJBquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Hi all
gt; gt; gt; gt;
gt; gt; gt; gt; I'm finding my way round the intricacies of VLookup and making some
gt; gt; gt; gt; progress, but am stumped by a small problem.
gt; gt; gt; gt;
gt; gt; gt; gt; I have a working VLookup in a worksheet called Staff
gt; gt; gt; gt;
gt; gt; gt; gt; =VLOOKUP(A3,'Report data'!$A$1:$P$9999,4,FALSE)
gt; gt; gt; gt;
gt; gt; gt; gt; which returns a name from the Report Data sheet if it's present, and a 0
gt; gt; gt; gt; if
gt; gt; gt; gt; not. However, rather than the zero I would like it to return the value of
gt; gt; gt; gt; column 2 in the Staff (active) worksheet.
gt; gt; gt; gt;
gt; gt; gt; gt; Can this be done with a nested function or does it need VBA?
gt; gt; gt; gt;
gt; gt; gt; gt; All comments gratefully received!
gt; gt; gt; gt;
gt; gt; gt; gt; Sue JB
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;