Hi,

why when I use vlookup on cells that have a formula in
does vlookup return me 0.00 values i.e.

if(vlookup($n10,banking!$c$10:banking!$j$500,2)
="","",vlookup($n10,banking!$c$10:banking!$j$500,2 ))

n10=20091

banking has 20091 but this is obtained automatically by :

=IF(J260,J26,"")

the values returned are sometimes correct or 0.00 AND if $N
(X) is blank ( no returned value ) it always returns me
0.00

anyone any ideas how to overcome this please

thanks

steve

See Help for VLOOKUP. As you have written the formula, VLOOKUP assumes
that banking!$C$10:banking!$C$500 is in ascending order (if that column
is not sorted, then you probably want to use the optional 4th argument).
As posted, if N10<banking!C10, then your formula will return #N/A.
Othewise it will search down banking!$C$10:banking!$C$500 until it finds
the first value =N10.

For concreteness, say that occurs in row 27. If banking!C27=N10, then
VLOOKUP will return banking!D27. If banking!C27N10, then VLOOKUP will
return banking!D26, even if there is a value farther down in
banking!$C$10:banking!$C$500 that is exactly equal to N10.

Jerry

steve alcock wrote:

Hi,

why when I use vlookup on cells that have a formula in
does vlookup return me 0.00 values i.e.

if(vlookup($n10,banking!$c$10:banking!$j$500,2)
="","",vlookup($n10,banking!$c$10:banking!$j$500,2 ))

n10=20091

banking has 20091 but this is obtained automatically by :

=IF(J260,J26,"")

the values returned are sometimes correct or 0.00 AND if $N
(X) is blank ( no returned value ) it always returns me
0.00

anyone any ideas how to overcome this please

thanks

steve.

Hi Jerry,

maybe I havn't made my point clear,

if say d3 has a formula in it but appears blank and I use
another cell :

if(d3="","",Vlookup( etc )

it returns this 0.00 sometimes why ?

regards

steve





-----Original Message-----
See Help for VLOOKUP. As you have written the formula,
VLOOKUP assumes
that banking!$C$10:banking!$C$500 is in ascending order
(if that column
is not sorted, then you probably want to use the optional
4th argument).
As posted, if N10<banking!C10, then your formula will
return #N/A.
Othewise it will search down banking!$C$10:banking!$C$500
until it finds
the first value =N10.

For concreteness, say that occurs in row 27. If banking!
C27=N10, then
VLOOKUP will return banking!D27. If banking!C27N10,
then VLOOKUP will
return banking!D26, even if there is a value farther down
in
banking!$C$10:banking!$C$500 that is exactly equal to N10.

Jerry

steve alcock wrote:

Hi,

why when I use vlookup on cells that have a formula in
does vlookup return me 0.00 values i.e.

if(vlookup($n10,banking!$c$10:banking!$j$500,2)
="","",vlookup($n10,banking!$c$10:banking!$j$500,2 ))

n10=20091

banking has 20091 but this is obtained automatically
by :

=IF(J260,J26,"")

the values returned are sometimes correct or 0.00 AND
if $N
(X) is blank ( no returned value ) it always returns me
0.00

anyone any ideas how to overcome this please

thanks

steve.

.

0.00 would have to be coming from the VLOOKUP, which leads back to my
previous reply.

Jerry

steve alcock wrote:

Hi Jerry,

maybe I havn't made my point clear,

if say d3 has a formula in it but appears blank and I use
another cell :

if(d3="","",Vlookup( etc )

it returns this 0.00 sometimes why ?

regards

steve


-----Original Message-----
See Help for VLOOKUP. As you have written the formula,

VLOOKUP assumes

that banking!$C$10:banking!$C$500 is in ascending order

(if that column

is not sorted, then you probably want to use the optional

4th argument).

As posted, if N10<banking!C10, then your formula will

return #N/A.

Othewise it will search down banking!$C$10:banking!$C$500

until it finds

the first value =N10.

For concreteness, say that occurs in row 27. If banking!

C27=N10, then

VLOOKUP will return banking!D27. If banking!C27N10,

then VLOOKUP will

return banking!D26, even if there is a value farther down

in

banking!$C$10:banking!$C$500 that is exactly equal to N10.

Jerry

steve alcock wrote:


Hi,

why when I use vlookup on cells that have a formula in
does vlookup return me 0.00 values i.e.

if(vlookup($n10,banking!$c$10:banking!$j$500, 2)
="","",vlookup($n10,banking!$c$10:banking!$j$50 0,2))

n10=20091

banking has 20091 but this is obtained automatically

by :

=IF(J260,J26,"")

the values returned are sometimes correct or 0.00 AND

if $N

(X) is blank ( no returned value ) it always returns me
0.00

anyone any ideas how to overcome this please

thanks

steve.

........which leads back to my previous reply.

Jerry - There's a circular reference toolbar to help you fix that <g

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Jerry W. Lewis" wrote in message
...
0.00 would have to be coming from the VLOOKUP, which leads back to my
previous reply.

Jerry

<snip