I am trying to search data from between various sheets of a single workbook
but my results are not quite what I expected. I have a single workbook in
which I am looking for different data on seperate sheets but which will
display data that initially originates from one sheet which is collected by
another and then displayed on a third.

The workbook is a data base of league players, on my first sheet tab I have
all of their contact details typed in, I then use this to transfer their
details onto other sheets using their reference No. and the VLOOKUP option.

My cell contains the calc =IF(A4<"",VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE),"")

where 'PREMIERSHIP - NR' is the name of the sheet I am searching from -
!$A$4:$O$203 is the range from that sheet and 6 (which is a cell holding a
written name) is the column holding the detail I want to find.

The calc works well and finds and displays the correct data when there is
something actually written or displlayed in it, but my difficulty is that in
many instances there is no data displayed for it to find €“ i.e. it is a blank
cell but returns as #N/A in some instances or 0 (zero) in others and this has
confused the hell out of me.

What I want to try to do is return the word if it is written in the target
cell (which it does do) or absolutely nothing if the target cell contains
nothing to report which in some instances it does not which is when it will
return the zero but in others there is a calc which only displays if filled
by other criteria being fullfiled and it is in this instance when I get the
#N/A returned.

Could anyone please help me out with a solution.

Try this:

=IF(ISERROR(VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE),"",VLOOKUP(A4,'PREMIERSHI P -
NR'!$A$4:$O$203,6,FALSE)))


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45754

A result of #N/A means the lookup_value A4 could not be found in your
lookup_table.

A result of 0 means the lookup_value A4 was found in your lookup_table but
the corresponding cell in column_index_ number 6 was either empty or
contains the number 0.

There are several ways to fix these. Here's one generic way:

=IF(ISNA(VLOOKUP(...)),"",IF(VLOOKUP(...)="","",VL OOKUP(...)))

Here's a more specific way based on the return value being TEXT:

=IF(ISTEXT(VLOOKUP(...)),VLOOKUP(...),"")

That will handle both the #N/A and the 0 (empty cell) problem with one less
lookup function call.

--
Biff
Microsoft Excel MVP


"Big Trev" wrote in message
...
I am trying to search data from between various sheets of a single workbook
but my results are not quite what I expected. I have a single workbook in
which I am looking for different data on seperate sheets but which will
display data that initially originates from one sheet which is collected
by
another and then displayed on a third.

The workbook is a data base of league players, on my first sheet tab I
have
all of their contact details typed in, I then use this to transfer their
details onto other sheets using their reference No. and the VLOOKUP
option.

My cell contains the calc =IF(A4<"",VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE),"")

where 'PREMIERSHIP - NR' is the name of the sheet I am searching from -
!$A$4:$O$203 is the range from that sheet and 6 (which is a cell holding a
written name) is the column holding the detail I want to find.

The calc works well and finds and displays the correct data when there is
something actually written or displlayed in it, but my difficulty is that
in
many instances there is no data displayed for it to find - i.e. it is a
blank
cell but returns as #N/A in some instances or 0 (zero) in others and this
has
confused the hell out of me.

What I want to try to do is return the word if it is written in the target
cell (which it does do) or absolutely nothing if the target cell contains
nothing to report which in some instances it does not which is when it
will
return the zero but in others there is a calc which only displays if
filled
by other criteria being fullfiled and it is in this instance when I get
the
#N/A returned.

Could anyone please help me out with a solution.

There is a misplaced closing ")" in the formula. Should be:

=IF(ISERROR(VLOOKUP(...)),"",VLOOKUP(...))

On a side note, you can save a few keystrokes by replacing FALSE with 0.


--
Biff
Microsoft Excel MVP


"JBeaucaire" wrote in message
...

Try this:

=IF(ISERROR(VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE),"",VLOOKUP(A4,'PREMIERSHI P -
NR'!$A$4:$O$203,6,FALSE)))


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile:
http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45754

I have put your suggestion in but unfortunately it will not accept this as it
says that too many arguments have been entered for this function - any more
ideas please.

"JBeaucaire" wrote:


Try this:

=IF(ISERROR(VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE),"",VLOOKUP(A4,'PREMIERSHI P -
NR'!$A$4:$O$203,6,FALSE)))


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45754

Thanks, I used the corrected ")" and it now works exactly as wanted

"T. Valko" wrote:

A result of #N/A means the lookup_value A4 could not be found in your
lookup_table.

A result of 0 means the lookup_value A4 was found in your lookup_table but
the corresponding cell in column_index_ number 6 was either empty or
contains the number 0.

There are several ways to fix these. Here's one generic way:

=IF(ISNA(VLOOKUP(...)),"",IF(VLOOKUP(...)="","",VL OOKUP(...)))

Here's a more specific way based on the return value being TEXT:

=IF(ISTEXT(VLOOKUP(...)),VLOOKUP(...),"")

That will handle both the #N/A and the 0 (empty cell) problem with one less
lookup function call.

--
Biff
Microsoft Excel MVP


"Big Trev" wrote in message
...
I am trying to search data from between various sheets of a single workbook
but my results are not quite what I expected. I have a single workbook in
which I am looking for different data on seperate sheets but which will
display data that initially originates from one sheet which is collected
by
another and then displayed on a third.

The workbook is a data base of league players, on my first sheet tab I
have
all of their contact details typed in, I then use this to transfer their
details onto other sheets using their reference No. and the VLOOKUP
option.

My cell contains the calc =IF(A4<"",VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE),"")

where 'PREMIERSHIP - NR' is the name of the sheet I am searching from -
!$A$4:$O$203 is the range from that sheet and 6 (which is a cell holding a
written name) is the column holding the detail I want to find.

The calc works well and finds and displays the correct data when there is
something actually written or displlayed in it, but my difficulty is that
in
many instances there is no data displayed for it to find - i.e. it is a
blank
cell but returns as #N/A in some instances or 0 (zero) in others and this
has
confused the hell out of me.

What I want to try to do is return the word if it is written in the target
cell (which it does do) or absolutely nothing if the target cell contains
nothing to report which in some instances it does not which is when it
will
return the zero but in others there is a calc which only displays if
filled
by other criteria being fullfiled and it is in this instance when I get
the
#N/A returned.

Could anyone please help me out with a solution.

I think he's got some of his parentheses in the wrong places.
What he probably intended was:
=IF(ISERROR(VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE)),"",VLOOKUP(A4,'PREMIERSH IP -
NR'!$A$4:$O$203,6,FALSE))
--
David Biddulph

Big Trev wrote:
I have put your suggestion in but unfortunately it will not accept
this as it says that too many arguments have been entered for this
function - any more ideas please.

"JBeaucaire" wrote:


Try this:

=IF(ISERROR(VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE),"",VLOOKUP(A4,'PREMIERSHI P -
NR'!$A$4:$O$203,6,FALSE)))

You're welcome!

--
Biff
Microsoft Excel MVP


"Big Trev" wrote in message
...
Thanks, I used the corrected ")" and it now works exactly as wanted

"T. Valko" wrote:

A result of #N/A means the lookup_value A4 could not be found in your
lookup_table.

A result of 0 means the lookup_value A4 was found in your lookup_table
but
the corresponding cell in column_index_ number 6 was either empty or
contains the number 0.

There are several ways to fix these. Here's one generic way:

=IF(ISNA(VLOOKUP(...)),"",IF(VLOOKUP(...)="","",VL OOKUP(...)))

Here's a more specific way based on the return value being TEXT:

=IF(ISTEXT(VLOOKUP(...)),VLOOKUP(...),"")

That will handle both the #N/A and the 0 (empty cell) problem with one
less
lookup function call.

--
Biff
Microsoft Excel MVP


"Big Trev" wrote in message
...
I am trying to search data from between various sheets of a single
workbook
but my results are not quite what I expected. I have a single workbook
in
which I am looking for different data on seperate sheets but which will
display data that initially originates from one sheet which is
collected
by
another and then displayed on a third.

The workbook is a data base of league players, on my first sheet tab I
have
all of their contact details typed in, I then use this to transfer
their
details onto other sheets using their reference No. and the VLOOKUP
option.

My cell contains the calc =IF(A4<"",VLOOKUP(A4,'PREMIERSHIP -
NR'!$A$4:$O$203,6,FALSE),"")

where 'PREMIERSHIP - NR' is the name of the sheet I am searching from -
!$A$4:$O$203 is the range from that sheet and 6 (which is a cell
holding a
written name) is the column holding the detail I want to find.

The calc works well and finds and displays the correct data when there
is
something actually written or displlayed in it, but my difficulty is
that
in
many instances there is no data displayed for it to find - i.e. it is a
blank
cell but returns as #N/A in some instances or 0 (zero) in others and
this
has
confused the hell out of me.

What I want to try to do is return the word if it is written in the
target
cell (which it does do) or absolutely nothing if the target cell
contains
nothing to report which in some instances it does not which is when it
will
return the zero but in others there is a calc which only displays if
filled
by other criteria being fullfiled and it is in this instance when I get
the
#N/A returned.

Could anyone please help me out with a solution.