I have a very large worksheet that I am using the following vlook up on

=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0))
I am using the results of the lookup for another calculation, so I don't
want the return value from my look up to be N/A. Can you please tell me how
to make it 0 instead of the N/A when there is no match in the lookup sheet.
Thanks

Hi
your formula should do exactly what you request and it works when i test it
.... if you're using excel 2002 or excel 2003 can you spot what is wrong if
you click on the cell with the formula and use tools / formula auditing /
evaluate formula to step through it?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JeaneIsaac" wrote in message
...
I have a very large worksheet that I am using the following vlook up on

=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0))
I am using the results of the lookup for another calculation, so I don't
want the return value from my look up to be N/A. Can you please tell me
how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks

It works "as-is" on XL2k also.........

Vaya con Dios,
Chuck, CABGx3


"JulieD" wrote in message
...
Hi
your formula should do exactly what you request and it works when i test
it
... if you're using excel 2002 or excel 2003 can you spot what is wrong if
you click on the cell with the formula and use tools / formula auditing /
evaluate formula to step through it?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"JeaneIsaac" wrote in message
...
I have a very large worksheet that I am using the following vlook up on


=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5)
,Dataentry!$D$9:$P$1508,12,0))
I am using the results of the lookup for another calculation, so I
don't
want the return value from my look up to be N/A. Can you please tell me
how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks

I have a similair problem except that I need to add two lookup values. How
do I write this formula if one or the other could be either zero or non
existant - possibly even both. Let's assume 1 Vlookup value is Z and another
is B. There are four possible situations and I probably need a nested if
stated (in the cell) but don't know how. Logically I want to say:

If (Z=TRUE) AND (B=TRUE) THEN "0"
ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)

"CLR" wrote:

It works "as-is" on XL2k also.........

Vaya con Dios,
Chuck, CABGx3


"JulieD" wrote in message
...
Hi
your formula should do exactly what you request and it works when i test
it
... if you're using excel 2002 or excel 2003 can you spot what is wrong if
you click on the cell with the formula and use tools / formula auditing /
evaluate formula to step through it?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"JeaneIsaac" wrote in message
...
I have a very large worksheet that I am using the following vlook up on


=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5)
,Dataentry!$D$9:$P$1508,12,0))
I am using the results of the lookup for another calculation, so I
don't
want the return value from my look up to be N/A. Can you please tell me
how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks

Hi

something along these lines?????

=IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z= B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array, col_num,true_or_false)))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JICDB" wrote in message
...
I have a similair problem except that I need to add two lookup values. How
do I write this formula if one or the other could be either zero or non
existant - possibly even both. Let's assume 1 Vlookup value is Z and
another
is B. There are four possible situations and I probably need a nested if
stated (in the cell) but don't know how. Logically I want to say:

If (Z=TRUE) AND (B=TRUE) THEN "0"
ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)

"CLR" wrote:

It works "as-is" on XL2k also.........

Vaya con Dios,
Chuck, CABGx3


"JulieD" wrote in message
...
Hi
your formula should do exactly what you request and it works when i
test
it
... if you're using excel 2002 or excel 2003 can you spot what is wrong
if
you click on the cell with the formula and use tools / formula auditing
/
evaluate formula to step through it?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"JeaneIsaac" wrote in message
...
I have a very large worksheet that I am using the following vlook up
on


=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5)
,Dataentry!$D$9:$P$1508,12,0))
I am using the results of the lookup for another calculation, so I
don't
want the return value from my look up to be N/A. Can you please tell
me
how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks

Thanks JulieD - I'll try it after I return from lunch. I was so frustrated
that I temporarily gave up. You've given me hope to try again.

"JulieD" wrote:

Hi

something along these lines?????

=IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z= B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array, col_num,true_or_false)))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JICDB" wrote in message
...
I have a similair problem except that I need to add two lookup values. How
do I write this formula if one or the other could be either zero or non
existant - possibly even both. Let's assume 1 Vlookup value is Z and
another
is B. There are four possible situations and I probably need a nested if
stated (in the cell) but don't know how. Logically I want to say:

If (Z=TRUE) AND (B=TRUE) THEN "0"
ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)

"CLR" wrote:

It works "as-is" on XL2k also.........

Vaya con Dios,
Chuck, CABGx3


"JulieD" wrote in message
...
Hi
your formula should do exactly what you request and it works when i
test
it
... if you're using excel 2002 or excel 2003 can you spot what is wrong
if
you click on the cell with the formula and use tools / formula auditing
/
evaluate formula to step through it?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"JeaneIsaac" wrote in message
...
I have a very large worksheet that I am using the following vlook up
on


=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5)
,Dataentry!$D$9:$P$1508,12,0))
I am using the results of the lookup for another calculation, so I
don't
want the return value from my look up to be N/A. Can you please tell
me
how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks

I understand all but the last section that begins with "table" Could you
tell me how this section should relate to my data?

"JulieD" wrote:

Hi

something along these lines?????

=IF(AND(Z=TRUE,B=TRUE),0,IF(AND(Z=FALSE,B=TRUE),Z= B,VLOOKUP(IF(AND(Z=TRUE,B=FALSE),B,Z),table_array, col_num,true_or_false)))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JICDB" wrote in message
...
I have a similair problem except that I need to add two lookup values. How
do I write this formula if one or the other could be either zero or non
existant - possibly even both. Let's assume 1 Vlookup value is Z and
another
is B. There are four possible situations and I probably need a nested if
stated (in the cell) but don't know how. Logically I want to say:

If (Z=TRUE) AND (B=TRUE) THEN "0"
ELSEIF (Z=TRUE) AND (B=FALSE) THEN B
ELSEIF (Z=FALSE) AND (B=TRUE) THEN Z
ELSE (Z=FALSE) AND (B=FALSE_ THEN (Z=B)

"CLR" wrote:

It works "as-is" on XL2k also.........

Vaya con Dios,
Chuck, CABGx3


"JulieD" wrote in message
...
Hi
your formula should do exactly what you request and it works when i
test
it
... if you're using excel 2002 or excel 2003 can you spot what is wrong
if
you click on the cell with the formula and use tools / formula auditing
/
evaluate formula to step through it?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
...well i'm working on it anyway
"JeaneIsaac" wrote in message
...
I have a very large worksheet that I am using the following vlook up
on


=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5)
,Dataentry!$D$9:$P$1508,12,0))
I am using the results of the lookup for another calculation, so I
don't
want the return value from my look up to be N/A. Can you please tell
me
how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks

Hi,

I am dealing with the same problem however, the formula is not working for
me (office 2007). The formula I use is:
=VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE)
I made out of this
=if(isna(VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE),0 ,VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE))

Now I get a message the the formula contains an error...

What is wrong?



"JulieD" wrote:

Hi
your formula should do exactly what you request and it works when i test it
.... if you're using excel 2002 or excel 2003 can you spot what is wrong if
you click on the cell with the formula and use tools / formula auditing /
evaluate formula to step through it?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JeaneIsaac" wrote in message
...
I have a very large worksheet that I am using the following vlook up on

=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0))
I am using the results of the lookup for another calculation, so I don't
want the return value from my look up to be N/A. Can you please tell me
how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks

Hi

=IF(ISNA(VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE)); 0;VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE))

Regards,
Per

"Vincent" skrev i meddelelsen
...
Hi,

I am dealing with the same problem however, the formula is not working for
me (office 2007). The formula I use is:
=VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE)
I made out of this
=if(isna(VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE),0 ,VLOOKUP(C2;Sheet1!$A$1:$E$5000;5;FALSE))

Now I get a message the the formula contains an error...

What is wrong?



"JulieD" wrote:

Hi
your formula should do exactly what you request and it works when i test
it
.... if you're using excel 2002 or excel 2003 can you spot what is wrong
if
you click on the cell with the formula and use tools / formula auditing /
evaluate formula to step through it?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JeaneIsaac" wrote in message
...
I have a very large worksheet that I am using the following vlook up on

=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0))
I am using the results of the lookup for another calculation, so I
don't
want the return value from my look up to be N/A. Can you please tell
me
how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks

Works A1 here - excel 2003

=IF(ISNA(VLOOKUP(G2,IPoperations!$A$2:$F$270,6,FAL SE)), "",
VLOOKUP(G2,IPoperations!$A$2:$F$270,6,FALSE))

I wanted "nothing" instead of 0.

"JeaneIsaac" wrote:

I have a very large worksheet that I am using the following vlook up on

=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0))
I am using the results of the lookup for another calculation, so I don't
want the return value from my look up to be N/A. Can you please tell me how
to make it 0 instead of the N/A when there is no match in the lookup sheet.
Thanks

You can use ISERROR like this:
=IF(ISERROR(A1/B1),"",A1/B1)

MathieuM;319393 Wrote:
Works A1 here - excel 2003

=IF(ISNA(VLOOKUP(G2,IPoperations!$A$2:$F$270,6,FAL SE)), "",
VLOOKUP(G2,IPoperations!$A$2:$F$270,6,FALSE))

I wanted "nothing" instead of 0.

"JeaneIsaac" wrote:

I have a very large worksheet that I am using the following vlook up
on


=IF(ISNA(VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0)),0,VLOOKUP(trim($D5),Dataentry!$D$9:$P$1508, 12,0))
I am using the results of the lookup for another calculation, so I
don't
want the return value from my look up to be N/A. Can you please tell
me how
to make it 0 instead of the N/A when there is no match in the lookup
sheet.
Thanks



--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=89297

How can I use the formula IF(ISERROR) or ISNA etc., if i have like 6
verticallookups in one cell? It is possible that 3 of them have an output and
the other 3 are NA.

Thanks in advance

Christian Hoedeman;400509 Wrote:
How can I use the formula IF(ISERROR) or ISNA etc., if i have like 6
verticallookups in one cell? It is possible that 3 of them have an
output and
the other 3 are NA.

Thanks in advanceProvide your formula and we will help you with that :)


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=89297

I am not sure if you are still working on it, but if you are just send a
lttle example!
If it is to big, let me know, I provide you my email!

"Simon Lloyd" wrote:


Christian Hoedeman;400509 Wrote:
How can I use the formula IF(ISERROR) or ISNA etc., if i have like 6
verticallookups in one cell? It is possible that 3 of them have an
output and
the other 3 are NA.

Thanks in advanceProvide your formula and we will help you with that :)


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=89297

I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and
obviously if there is no data for it to pull I get the #N/A. How do I hide
it if there is no data with this type of equation?

Thanks,

One way...

=IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...)))

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and
obviously if there is no data for it to pull I get the #N/A. How do I
hide
it if there is no data with this type of equation?

Thanks,

Woof, okay... I see where my current equation goes, but what goes in the
parenthesis behind the first MATCH? and the quote marks?


"T. Valko" wrote:

One way...

=IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...)))

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and
obviously if there is no data for it to pull I get the #N/A. How do I
hide
it if there is no data with this type of equation?

Thanks,

Post the formula that's giving you the #N/A.

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
Woof, okay... I see where my current equation goes, but what goes in the
parenthesis behind the first MATCH? and the quote marks?


"T. Valko" wrote:

One way...

=IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...)))

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and
obviously if there is no data for it to pull I get the #N/A. How do I
hide
it if there is no data with this type of equation?

Thanks,

It is as follows:

{=INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'FIELD'$E $31,CEMS!$B$2;$B$600&CEMS!$D$2:$D$600,0))}

Sorry it took me a while to get back to this.

"T. Valko" wrote:

Post the formula that's giving you the #N/A.

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
Woof, okay... I see where my current equation goes, but what goes in the
parenthesis behind the first MATCH? and the quote marks?


"T. Valko" wrote:

One way...

=IF(ISNA(MATCH(...)),"",INDEX(...,MATCH(...)))

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
I am using a {=INDEX(MATCH())} equation to populate a spreadsheet and
obviously if there is no data for it to pull I get the #N/A. How do I
hide
it if there is no data with this type of equation?

Thanks,