|
How do I remove the #div or N/A and make it 0 when using VLookup?
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 |
How do I remove the #div or N/A and make it 0 when using VLook
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 |
How do I remove the #div or N/A and make it 0 when using VLook
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 |
How do I remove the #div or N/A and make it 0 when using VLookup?
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 |
How do I remove the #div or N/A and make it 0 when using VLookup?
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 do I remove the #div or N/A and make it 0 when using VLook
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 |
How do I remove the #div or N/A and make it 0 when using VLookup?
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 |
How do I remove the #div or N/A and make it 0 when using VLook
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 |
How do I remove the #div or N/A and make it 0 when using VLookup?
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, |
How do I remove the #div or N/A and make it 0 when using VLookup?
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
How do I remove the #div or N/A and make it 0 when using VLook
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, |
| All times are GMT +1. The time now is 02:18 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com