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

Try using =IF(ISERROR(VLOOKUP(TRIM($G12),
instead of (ISNA(Vlookup(Trim$g$12
That took the N/A out of my worksheet. Hope this works for you.
JCI


JICDB" wrote:

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 understood that you were wanting to use the VLOOKUP function with the
possibility of two lookup values Z & B
when you said
---
Let's assume 1 Vlookup value is Z and another is B.
----
the VLOOKUP function as four parameters
=VLOOKUP(lookup_value, table_array,col_#_of_data_to_return,approx_match)
the lookup_value is either Z or B depending on the result of the IF
statement
however, you still need to fill in the rest of the VLOOKUP statement

if this doesn't make sense, please try explaining what you're after again
using the "real" data that you have because i found the Z & B concept a bit
difficult to comprehend.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"JICDB" wrote in message
...
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,

Try one of these (both still array entered):

=IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0)))

If you're using Excel 2007:

=IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ")

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
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,

Both worked superbly... Think I'll stick with the second one though.

Thank you very much.

"T. Valko" wrote:

Try one of these (both still array entered):

=IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0)))

If you're using Excel 2007:

=IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ")

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
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,

The second one masks all errors, not just the NA

So be careful.


Gord Dibben MS Excel MVP

On Sat, 19 Sep 2009 14:43:01 -0700, Flcnmech
wrote:

Both worked superbly... Think I'll stick with the second one though.

Thank you very much.

"T. Valko" wrote:

Try one of these (both still array entered):

=IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0)))

If you're using Excel 2007:

=IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ")

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
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,

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
Both worked superbly... Think I'll stick with the second one though.

Thank you very much.

"T. Valko" wrote:

Try one of these (both still array entered):

=IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0)))

If you're using Excel 2007:

=IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ")

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
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,

Okay... now I am in trouble... Migrated to the new office and due to the
data pull program had to save my working spreadsheet as a xlsm now the arrays
don't work... What do I need to do?

If I 'reset' the equation as an array equation then it pulls the data in,
but it will not do it on it's own when I open the spreadsheet.

Thanks in advance.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
Both worked superbly... Think I'll stick with the second one though.

Thank you very much.

"T. Valko" wrote:

Try one of these (both still array entered):

=IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0)))

If you're using Excel 2007:

=IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ")

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
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,

Sorry, i was incorrect... it is now in an xlsx format not an xlsm.

"Flcnmech" wrote:

Okay... now I am in trouble... Migrated to the new office and due to the
data pull program had to save my working spreadsheet as a xlsm now the arrays
don't work... What do I need to do?

If I 'reset' the equation as an array equation then it pulls the data in,
but it will not do it on it's own when I open the spreadsheet.

Thanks in advance.

"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
Both worked superbly... Think I'll stick with the second one though.

Thank you very much.

"T. Valko" wrote:

Try one of these (both still array entered):

=IF(ISNA(MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$ B$600&CEMS!$D$2:$D$600,0)),"",INDEX(CEMS!K$2:$K$60 0,MATCH('FIELD'!B8&'FIELD'$E$31,CEMS!$B$2:$B$600&C EMS!$D$2:$D$600,0)))

If you're using Excel 2007:

=IFERROR(INDEX(CEMS!K$2:$K$600,MATCH('FIELD'!B8&'F IELD'$E$31,CEMS!$B$2:$B$600&CEMS!$D$2:$D$600,0))," ")

--
Biff
Microsoft Excel MVP


"Flcnmech" wrote in message
...
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,