| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
#NA
I have a VLOOKUP that checks the information of 2 cells against a table to
determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#2
|
|||
|
|||
|
=vlookup(1,d2:d22,1,0)
=isna(vlookup(1,d2:d22,1,0)) =IF(ISNA(VLOOKUP(1,D2:D22,1,0)),"",1) maybe you want =IF(or(ISNA(VLOOKUP(1,D2:D22,1,0),b12<o12)),"",1) -- Don Guillett SalesAid Software "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#3
|
|||
|
|||
|
Could you explain your answer? I am not sure how this all comes together into
a one cell formula. "Don Guillett" wrote: =vlookup(1,d2:d22,1,0) =isna(vlookup(1,d2:d22,1,0)) =IF(ISNA(VLOOKUP(1,D2:D22,1,0)),"",1) maybe you want =IF(or(ISNA(VLOOKUP(1,D2:D22,1,0),b12<o12)),"",1) -- Don Guillett SalesAid Software "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#4
|
|||
|
|||
|
Maybe.........
=IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,F ALSE))),"",VLOOKUP(C12,$K$ 14:$O$18,4,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#6
|
|||
|
|||
|
I used the formula that you provided below and it worked fine except that it
doesn't really check the first part of the equation, only checks to see if something is there. If I have something in field B12 and nothing in C12 my results are blank, which is correct. If I have nothing in B12 and something in C12, I get an incorrect answer. I should get the same result "Blank" Any idea why this happens? "CLR" wrote: Maybe......... =IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,F ALSE))),"",VLOOKUP(C12,$K$ 14:$O$18,4,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#7
|
|||
|
|||
|
Try this.........
=IF(OR(B12=0,O12=0),"",IF($B12<$O$12,VLOOKUP(C12,$ K$14:$O$18,4,FALSE),"")) Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I used the formula that you provided below and it worked fine except that it doesn't really check the first part of the equation, only checks to see if something is there. If I have something in field B12 and nothing in C12 my results are blank, which is correct. If I have nothing in B12 and something in C12, I get an incorrect answer. I should get the same result "Blank" Any idea why this happens? "CLR" wrote: Maybe......... =IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,F ALSE))),"",VLOOKUP(C12,$K$ 14:$O$18,4,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#8
|
|||
|
|||
|
OK, It is almost there, but one more problem seems to be occurring with this
formula now. Here are the input cells =IF(OR($B12=0,$C12=0),"",IF($B12<$O$12,VLOOKUP($C1 2,$K$14:$O$18,4,FALSE),"")) B12 C12 4/2/2005 1234 K L M N O Poduct Bid 1/1/2005 4/1/2005 (Row12) Number Material Cost Cost Cost (Row13) 1195 2500/1-1/2" $58.80 $61.00 $64.00 (Row14) 1234 3000/1-1/2" $60.00 $63.00 $66.00 (Row15) 1194 4000/1-1/2" $65.00 $63.25 $66.25 (Row16) 1196 3000/1" $61.00 $64.00 $67.00 (row17) 1194 4000/1" $65.50 $67.00 $70.00 (Row18) I can't seem to get an answer when the date field matches or exceeds the "4/1/2005" criteria. All I get is blank! How do I setup the second condition portion in the formula. "CLR" wrote: Try this......... =IF(OR(B12=0,O12=0),"",IF($B12<$O$12,VLOOKUP(C12,$ K$14:$O$18,4,FALSE),"")) Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I used the formula that you provided below and it worked fine except that it doesn't really check the first part of the equation, only checks to see if something is there. If I have something in field B12 and nothing in C12 my results are blank, which is correct. If I have nothing in B12 and something in C12, I get an incorrect answer. I should get the same result "Blank" Any idea why this happens? "CLR" wrote: Maybe......... =IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,F ALSE))),"",VLOOKUP(C12,$K$ 14:$O$18,4,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#9
|
|||
|
|||
|
I'm sorry, I must have mis-understood.........I thought you only wanted an
answer when B12<O12.......then to look up C12 in the table. Now you seem to want something else........that's ok, I just don't understand what it is yet. Which cell is "date field".....where does the 4/1/2005 criteria" come in? what do you want to happen when B12=O12?........ It's past my bedtime now, but if you post back and someone doesn't answer right away, I'll give it another stab tomorrow........ Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... OK, It is almost there, but one more problem seems to be occurring with this formula now. Here are the input cells =IF(OR($B12=0,$C12=0),"",IF($B12<$O$12,VLOOKUP($C1 2,$K$14:$O$18,4,FALSE),"") ) B12 C12 4/2/2005 1234 K L M N O Poduct Bid 1/1/2005 4/1/2005 (Row12) Number Material Cost Cost Cost (Row13) 1195 2500/1-1/2" $58.80 $61.00 $64.00 (Row14) 1234 3000/1-1/2" $60.00 $63.00 $66.00 (Row15) 1194 4000/1-1/2" $65.00 $63.25 $66.25 (Row16) 1196 3000/1" $61.00 $64.00 $67.00 (row17) 1194 4000/1" $65.50 $67.00 $70.00 (Row18) I can't seem to get an answer when the date field matches or exceeds the "4/1/2005" criteria. All I get is blank! How do I setup the second condition portion in the formula. "CLR" wrote: Try this......... =IF(OR(B12=0,O12=0),"",IF($B12<$O$12,VLOOKUP(C12,$ K$14:$O$18,4,FALSE),"")) Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I used the formula that you provided below and it worked fine except that it doesn't really check the first part of the equation, only checks to see if something is there. If I have something in field B12 and nothing in C12 my results are blank, which is correct. If I have nothing in B12 and something in C12, I get an incorrect answer. I should get the same result "Blank" Any idea why this happens? "CLR" wrote: Maybe......... =IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,F ALSE))),"",VLOOKUP(C12,$K$ 14:$O$18,4,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#10
|
|||
|
|||
|
Hi Brian......
I looked at it again, and it might be that this is what you're after...... =IF(OR($B12=0,$C12=0),"",IF($B12<$O$12,VLOOKUP($C1 2,$K$14:$O$18,4,FALSE),VLOOKUP($C$12,$K$14:$O$18,5 ,FALSE))) Vaya con Dios, Chuck, CABGx3 "Brian" wrote: OK, It is almost there, but one more problem seems to be occurring with this formula now. Here are the input cells =IF(OR($B12=0,$C12=0),"",IF($B12<$O$12,VLOOKUP($C1 2,$K$14:$O$18,4,FALSE),"")) B12 C12 4/2/2005 1234 K L M N O Poduct Bid 1/1/2005 4/1/2005 (Row12) Number Material Cost Cost Cost (Row13) 1195 2500/1-1/2" $58.80 $61.00 $64.00 (Row14) 1234 3000/1-1/2" $60.00 $63.00 $66.00 (Row15) 1194 4000/1-1/2" $65.00 $63.25 $66.25 (Row16) 1196 3000/1" $61.00 $64.00 $67.00 (row17) 1194 4000/1" $65.50 $67.00 $70.00 (Row18) I can't seem to get an answer when the date field matches or exceeds the "4/1/2005" criteria. All I get is blank! How do I setup the second condition portion in the formula. "CLR" wrote: Try this......... =IF(OR(B12=0,O12=0),"",IF($B12<$O$12,VLOOKUP(C12,$ K$14:$O$18,4,FALSE),"")) Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I used the formula that you provided below and it worked fine except that it doesn't really check the first part of the equation, only checks to see if something is there. If I have something in field B12 and nothing in C12 my results are blank, which is correct. If I have nothing in B12 and something in C12, I get an incorrect answer. I should get the same result "Blank" Any idea why this happens? "CLR" wrote: Maybe......... =IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,F ALSE))),"",VLOOKUP(C12,$K$ 14:$O$18,4,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#11
|
|||
|
|||
|
Thanks for your help. The last one worked perfect.
"CLR" wrote: Hi Brian...... I looked at it again, and it might be that this is what you're after...... =IF(OR($B12=0,$C12=0),"",IF($B12<$O$12,VLOOKUP($C1 2,$K$14:$O$18,4,FALSE),VLOOKUP($C$12,$K$14:$O$18,5 ,FALSE))) Vaya con Dios, Chuck, CABGx3 "Brian" wrote: OK, It is almost there, but one more problem seems to be occurring with this formula now. Here are the input cells =IF(OR($B12=0,$C12=0),"",IF($B12<$O$12,VLOOKUP($C1 2,$K$14:$O$18,4,FALSE),"")) B12 C12 4/2/2005 1234 K L M N O Poduct Bid 1/1/2005 4/1/2005 (Row12) Number Material Cost Cost Cost (Row13) 1195 2500/1-1/2" $58.80 $61.00 $64.00 (Row14) 1234 3000/1-1/2" $60.00 $63.00 $66.00 (Row15) 1194 4000/1-1/2" $65.00 $63.25 $66.25 (Row16) 1196 3000/1" $61.00 $64.00 $67.00 (row17) 1194 4000/1" $65.50 $67.00 $70.00 (Row18) I can't seem to get an answer when the date field matches or exceeds the "4/1/2005" criteria. All I get is blank! How do I setup the second condition portion in the formula. "CLR" wrote: Try this......... =IF(OR(B12=0,O12=0),"",IF($B12<$O$12,VLOOKUP(C12,$ K$14:$O$18,4,FALSE),"")) Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I used the formula that you provided below and it worked fine except that it doesn't really check the first part of the equation, only checks to see if something is there. If I have something in field B12 and nothing in C12 my results are blank, which is correct. If I have nothing in B12 and something in C12, I get an incorrect answer. I should get the same result "Blank" Any idea why this happens? "CLR" wrote: Maybe......... =IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,F ALSE))),"",VLOOKUP(C12,$K$ 14:$O$18,4,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
|
#12
|
|||
|
|||
|
You're most welcome........glad you got it working, and thanks for the
feedback.... Vaya con Dios, Chuck, CABGx3 "Brian" wrote: Thanks for your help. The last one worked perfect. "CLR" wrote: Hi Brian...... I looked at it again, and it might be that this is what you're after...... =IF(OR($B12=0,$C12=0),"",IF($B12<$O$12,VLOOKUP($C1 2,$K$14:$O$18,4,FALSE),VLOOKUP($C$12,$K$14:$O$18,5 ,FALSE))) Vaya con Dios, Chuck, CABGx3 "Brian" wrote: OK, It is almost there, but one more problem seems to be occurring with this formula now. Here are the input cells =IF(OR($B12=0,$C12=0),"",IF($B12<$O$12,VLOOKUP($C1 2,$K$14:$O$18,4,FALSE),"")) B12 C12 4/2/2005 1234 K L M N O Poduct Bid 1/1/2005 4/1/2005 (Row12) Number Material Cost Cost Cost (Row13) 1195 2500/1-1/2" $58.80 $61.00 $64.00 (Row14) 1234 3000/1-1/2" $60.00 $63.00 $66.00 (Row15) 1194 4000/1-1/2" $65.00 $63.25 $66.25 (Row16) 1196 3000/1" $61.00 $64.00 $67.00 (row17) 1194 4000/1" $65.50 $67.00 $70.00 (Row18) I can't seem to get an answer when the date field matches or exceeds the "4/1/2005" criteria. All I get is blank! How do I setup the second condition portion in the formula. "CLR" wrote: Try this......... =IF(OR(B12=0,O12=0),"",IF($B12<$O$12,VLOOKUP(C12,$ K$14:$O$18,4,FALSE),"")) Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I used the formula that you provided below and it worked fine except that it doesn't really check the first part of the equation, only checks to see if something is there. If I have something in field B12 and nothing in C12 my results are blank, which is correct. If I have nothing in B12 and something in C12, I get an incorrect answer. I should get the same result "Blank" Any idea why this happens? "CLR" wrote: Maybe......... =IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,F ALSE))),"",VLOOKUP(C12,$K$ 14:$O$18,4,FALSE)) All on one line, watch out for email word-wrap Vaya con Dios, Chuck, CABGx3 "Brian" wrote in message ... I have a VLOOKUP that checks the information of 2 cells against a table to determine the proper information to be displayed. It works fine except when there is not any information yet placed in the cells. The that #NA shows up when I just want it to be blank. I tried the ISNA, but I think I am doing something wrong because I am getting the Error "You Have Entered Too Many Arguments For This Function" Here is my function. What am I doing wrong? =IF(ISNA($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALS E)),"",VLOOKUP(C12,$K$14:$ O$18,5,FALSE)) It works fine if I remove the (ISNA and the ),"" piece. Help me |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
