Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SueJB
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Arvi Laanemets
 
Posts: n/a
Default Return alternate value if VLookup can't find match

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<attarkon.ee )



"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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SueJB
 
Posts: n/a
Default Return alternate value if VLookup can't find match

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<attarkon.ee )



"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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Niek Otten
 
Posts: n/a
Default Return alternate value if VLookup can't find match

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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SueJB
 
Posts: n/a
Default Return alternate value if VLookup can't find match

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






  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mariano
 
Posts: n/a
Default Return alternate value if VLookup can't find match

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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Kleev
 
Posts: n/a
Default Return alternate value if VLookup can't find match

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




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
SueJB
 
Posts: n/a
Default Return alternate value if VLookup can't find match

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
FIND FORMULA SIMILAR TO VLOOKUP FOR NON-ASCENDING VALUES Kir Excel Worksheet Functions 2 November 10th 05 10:39 PM
Find text in another workbook and paste if found match - VBA Pasmatos Excel Discussion (Misc queries) 1 November 10th 05 02:00 PM
How to find MATCH numbers ? toyota58 Excel Worksheet Functions 8 September 20th 05 09:33 AM
is there a way to search with vlookup to match more than 1 column puppy Excel Discussion (Misc queries) 7 June 30th 05 07:41 PM
Use MATCH to find position of max in 2D range? Peter B Excel Worksheet Functions 4 October 28th 04 05:23 PM


All times are GMT +1. The time now is 11:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"