Combining ISNA and LEN functions

  • Thread starter Thread starter Flats
  • Start date Start date
F

Flats

Hi all,

I'm trying to develop a vlookup formula that returns a value if one
exists but does NOT return either #N/A or "0", I would like the field
left blank if there is no value.

I can do either/or but not combine both requirements.

i.e
=IF(ISNA(VLOOKUP($A5,'Game 1'!$A$4:$N$11,12,0)),"",VLOOKUP($A5,'Game
1'!$A$4:$N$11,12,0))

or

=IF(LEN(VLOOKUP($A5,'Game 1'!$A$4:$N$11,12,0))=0,"",(VLOOKUP($A5,'Game
1'!$A$4:$N$11,12,0)))


I though combining them would do the trick ie:

=IF(ISNA(VLOOKUP($A5,'Game
1'!$A$4:$N$11,12,0)),"",LEN(VLOOKUP($A5,'Game
1'!$A$4:$N$11,12,0))=0)))
or something similar but no joy, instead it returns TRUE or FALSE

Can somebody please help.
 
Hi
try
=IF(OR(ISNA(VLOOKUP($A5,'Game
1'!$A$4:$N$11,12,0)),LEN(VLOOKUP($A5,'Game
1'!$A$4:$N$11,12,0))=0),"",VLOOKUP($A5,'Game1'!$A$4:$N$11,12,0))
 
Flats

Try this:

=IF(ISERROR(VLOOKUP($A5,'Game 1'!$A$4:$N$11,12,0)),"",VLOOKUP($A5,'Game
1'!$A$4:$N$11,12,0))

Andy.
 
Hi,

Thanks for the quick response, unfortunately there seems to be
problem. When the 'player' exists it works fine and returns either
value or a blank.

However when the 'player' did not exist (ie didn't play that game) I'
still getting #N/A

In the cell where the player didn't exist if I press the fx button,
to open the function Arguements box, the logical_test says this :

OR(ISNA(VLOOKUP($A8,'Game 1'!$A$4:$N$11,12,0)),LEN(VLOOKUP($A8,'Gam
1'!$A$4:$N$11,12,0))=0)

= #N/A (ie not a TRUE or FALSE)

is there a problem with the syntax?

Thanks in advanc
 
Hi,

Thanks for the quick response, unfortunately there seems to be
problem. When the 'player' exists it works fine and returns either
value or a blank.

However when the 'player' did not exist (ie didn't play that game) I'
still getting #N/A

In the cell where the player didn't exist if I press the fx button,
to open the function Arguements box, the logical_test says this :

OR(ISNA(VLOOKUP($A8,'Game 1'!$A$4:$N$11,12,0)),LEN(VLOOKUP($A8,'Gam
1'!$A$4:$N$11,12,0))=0)

= #N/A (ie not a TRUE or FALSE)

is there a problem with the syntax?

Thanks in advanc
 
Hi Frank,

Yes I tried your suggestion, my previous post was directed to you (th
one that was repeated).

Basically it eliminated the "0"s but I still got the #N/A's

The formula seemed to have a problem... I tried to detail as best
could in the previous post (see about 3 posts above
 
Hi
the following should work

=IF(ISNA(VLOOKUP($A5,'Game1'!$A$4:$N$11,12,0)),"",IF(LEN(VLOOKUP($A5,'G
ame1'!$A$4:$N$11,12,0))=0,"",VLOOKUP($A5,'Game1'!$A$4:$N$11,12,0)))
 
One more way:

=IF(ISERROR(1/LEN(VLOOKUP($A5,Game1!$A$4:$N$11,12,0))),"",
VLOOKUP($A5,Game1!$A$5:$N$11,12,0))
 
Back
Top