#NA #3

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,FALSE)),"",VLOOKUP(C12,$K$14:$O$18,5,FALSE))

It works fine if I remove the (ISNA and the ),"" piece.
Help me
0
Brian8214 (439)
3/7/2005 7:35:07 PM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
462 Views

Similar Articles

[PageSpeed] 19

=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
donaldb@281.com
"Brian" <Brian@discussions.microsoft.com> wrote in message
news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> 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,FALSE)),"",VLOOKUP(C12,$K$14:$
O$18,5,FALSE))
>
> It works fine if I remove the (ISNA and the ),"" piece.
> Help me


0
Don
3/7/2005 7:44:39 PM
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
> donaldb@281.com
> "Brian" <Brian@discussions.microsoft.com> wrote in message
> news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> O$18,5,FALSE))
> >
> > It works fine if I remove the (ISNA and the ),"" piece.
> > Help me
> 
> 
> 
0
Brian8214 (439)
3/7/2005 9:11:02 PM
Maybe.........

=IF(ISNA(IF($B12<$O$12,VLOOKUP(C12,$K$14:$O$18,4,FALSE))),"",VLOOKUP(C12,$K$
14:$O$18,4,FALSE))

All on one line, watch out for email word-wrap

Vaya con Dios,
Chuck, CABGx3






"Brian" <Brian@discussions.microsoft.com> wrote in message
news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> 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,FALSE)),"",VLOOKUP(C12,$K$14:$
O$18,5,FALSE))
>
> It works fine if I remove the (ISNA and the ),"" piece.
> Help me


0
croberts (1377)
3/7/2005 9:48:49 PM
I was trying to show you how a formula comes about as parts of a whole. Did
you try the last line changing the range to yours and the , "",1 to  ,"",
your formula


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Brian" <Brian@discussions.microsoft.com> wrote in message
news:DF0AAB5D-1949-4EEF-9483-6DC45627C7AB@microsoft.com...
> 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
> > donaldb@281.com
> > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> > O$18,5,FALSE))
> > >
> > > It works fine if I remove the (ISNA and the ),"" piece.
> > > Help me
> >
> >
> >


0
Don
3/7/2005 11:14:41 PM
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,FALSE))),"",VLOOKUP(C12,$K$
> 14:$O$18,4,FALSE))
> 
> All on one line, watch out for email word-wrap
> 
> Vaya con Dios,
> Chuck, CABGx3
> 
> 
> 
> 
> 
> 
> "Brian" <Brian@discussions.microsoft.com> wrote in message
> news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> O$18,5,FALSE))
> >
> > It works fine if I remove the (ISNA and the ),"" piece.
> > Help me
> 
> 
> 
0
Brian8214 (439)
3/14/2005 11:41:01 PM
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" <Brian@discussions.microsoft.com> wrote in message
news:4127BAEE-C754-4E1B-9FC5-B71141DAFA02@microsoft.com...
> 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,FALSE))),"",VLOOKUP(C12,$K$
> > 14:$O$18,4,FALSE))
> >
> > All on one line, watch out for email word-wrap
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> >
> >
> >
> > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> > O$18,5,FALSE))
> > >
> > > It works fine if I remove the (ISNA and the ),"" piece.
> > > Help me
> >
> >
> >


0
croberts (1377)
3/15/2005 12:37:41 AM
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($C12,$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" <Brian@discussions.microsoft.com> wrote in message
> news:4127BAEE-C754-4E1B-9FC5-B71141DAFA02@microsoft.com...
> > 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,FALSE))),"",VLOOKUP(C12,$K$
> > > 14:$O$18,4,FALSE))
> > >
> > > All on one line, watch out for email word-wrap
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > > > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> > > O$18,5,FALSE))
> > > >
> > > > It works fine if I remove the (ISNA and the ),"" piece.
> > > > Help me
> > >
> > >
> > >
> 
> 
> 
0
Brian8214 (439)
3/15/2005 1:17:02 AM
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" <Brian@discussions.microsoft.com> wrote in message
news:AA7973C4-50B1-4A26-A0EB-3B111E800474@microsoft.com...
> 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($C12,$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" <Brian@discussions.microsoft.com> wrote in message
> > news:4127BAEE-C754-4E1B-9FC5-B71141DAFA02@microsoft.com...
> > > 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,FALSE))),"",VLOOKUP(C12,$K$
> > > > 14:$O$18,4,FALSE))
> > > >
> > > > All on one line, watch out for email word-wrap
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > > news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > > > > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> > > > O$18,5,FALSE))
> > > > >
> > > > > It works fine if I remove the (ISNA and the ),"" piece.
> > > > > Help me
> > > >
> > > >
> > > >
> >
> >
> >


0
croberts (1377)
3/15/2005 1:40:43 AM
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($C12,$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($C12,$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" <Brian@discussions.microsoft.com> wrote in message
> > news:4127BAEE-C754-4E1B-9FC5-B71141DAFA02@microsoft.com...
> > > 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,FALSE))),"",VLOOKUP(C12,$K$
> > > > 14:$O$18,4,FALSE))
> > > >
> > > > All on one line, watch out for email word-wrap
> > > >
> > > > Vaya con Dios,
> > > > Chuck, CABGx3
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > > news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > > > > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> > > > O$18,5,FALSE))
> > > > >
> > > > > It works fine if I remove the (ISNA and the ),"" piece.
> > > > > Help me
> > > >
> > > >
> > > >
> > 
> > 
> > 
0
CLR (807)
3/15/2005 4:37:10 PM
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($C12,$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($C12,$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" <Brian@discussions.microsoft.com> wrote in message
> > > news:4127BAEE-C754-4E1B-9FC5-B71141DAFA02@microsoft.com...
> > > > 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,FALSE))),"",VLOOKUP(C12,$K$
> > > > > 14:$O$18,4,FALSE))
> > > > >
> > > > > All on one line, watch out for email word-wrap
> > > > >
> > > > > Vaya con Dios,
> > > > > Chuck, CABGx3
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > > > news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > > > > > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> > > > > O$18,5,FALSE))
> > > > > >
> > > > > > It works fine if I remove the (ISNA and the ),"" piece.
> > > > > > Help me
> > > > >
> > > > >
> > > > >
> > > 
> > > 
> > > 
0
Brian8214 (439)
3/15/2005 5:19:02 PM
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($C12,$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($C12,$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" <Brian@discussions.microsoft.com> wrote in message
> > > > news:4127BAEE-C754-4E1B-9FC5-B71141DAFA02@microsoft.com...
> > > > > 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,FALSE))),"",VLOOKUP(C12,$K$
> > > > > > 14:$O$18,4,FALSE))
> > > > > >
> > > > > > All on one line, watch out for email word-wrap
> > > > > >
> > > > > > Vaya con Dios,
> > > > > > Chuck, CABGx3
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Brian" <Brian@discussions.microsoft.com> wrote in message
> > > > > > news:31DCDD56-6568-4B5E-A161-B9502EF21A98@microsoft.com...
> > > > > > > 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,FALSE)),"",VLOOKUP(C12,$K$14:$
> > > > > > O$18,5,FALSE))
> > > > > > >
> > > > > > > It works fine if I remove the (ISNA and the ),"" piece.
> > > > > > > Help me
> > > > > >
> > > > > >
> > > > > >
> > > > 
> > > > 
> > > > 
0
CLR (807)
3/15/2005 5:23:07 PM
Reply:

Similar Artilces:

na() and #na label displayed on graphs...
Hi all, I have got a list of data and some are resulting from the NA() function. Objective is to avoid a 0 value plot on the graph..... and it is working great. BUT On the graph, I set the label of each ploy to "value". And unfortunately a #NA label is displayed on the graph on each plot where there is the NA()... How can I remove the #NA from my graph ? Thks, AL. In a column or bar chart, you may as well use "". NA() is what works only for marker series (line, XY, and some radar series) by completely hiding the point. In column and bars, the point is plotted, but h...

default From field #3
Is there any way to set a default value for "from" field in new messages (the from account may be different from the account that log on the server)? thanks ...

DLL Linker error LNK2001 (Visual C++) #3
DLL Linker error LNK2001 (Visual C++) I have made a Microsoft Visual C++ dialog Projekt. And it wroks. Now I have made the same project as DLL. With a test progamm I call my dialog in the DLL . It works so far. But when I have in my DLL dialog class a member like COptionen m_dOptionen; I cann compile it. But when I link my testprogram I get the Linker error : LNK2001: unresolved external symbol public: __thiscallCOptionen::COptionen(class CWnd *)"(??0COptionen@@QAE@PAVCWnd@@@Z) What's wrong? With a member like CDialog m_dOptionen it works. The linker knows the class CDial...

#NA
Hi Is there a way to check that if a vlookup command returns a NA then it puts in a 0 not a NA? I have a list where I run vlookups but if the reference does not exist in the lookup table it returns a NA. I realise that I can run a ISNA and check but I was hoping to have something similar to =if(=vlookup(lookup,table,column,false)=NA,0,=vlookup(......)) Thanks Carlob1 Hi use =IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)) -- Regards Frank Kabel Frankfurt, Germany "Carlo" <carlob@global.co.za> schrieb im Newsbeitrag news:bfbc8513.0408150315.638088a3@posting.google.com... > Hi >...

CRM 3.0 for current customer...when do I get this?
I have an active service agreement, but still haven't received CRM 3.0, even though I've read here that it shipped December 6. Also, it's not available for download from Customer Source and we're still listed as being licensed for 1.2 only. Can anyone please explain the process for acquiring 3.0? Thank you. It depends on the type of Software Assurance you own (Enterprise licensees will receive the upgrade license and media automatically), but most SA licensees will have to ask their MBS reseller to order the license keys and media, for a nominal fee (shipping and hand...

Undeliverable #3
I am running SBS 2000 SP4 and I have certain users who's addresses come back as undeliverable unless you fully complete their addresses when typing (not auto-complete). Let's say Our email server (hosted by our ISP) is named 'thatdomain'. Our domain on SBS running Exchange is named 'thisdomain'. When I look at their profilesand email address settings they seem to be identical to the other users who experience no problems. However, when I export the address book into Excel and scroll to the EmailAddress field they show this type of address- '/o=thisdomain/ou...

unable to Customize Outlook Today #3
In my organization, we are using Exchange Server with Outlook 2000 on the workstations. On some of the workstations, when you click on the Customize Outlook Today button, nothing happens. I've thoroughly gone through the configuration and can't figure out why it doesn't work on some. Barry <anonymous@discussions.microsoft.com> wrote: > In my organization, we are using Exchange Server with > Outlook 2000 on the workstations. On some of the > workstations, when you click on the Customize Outlook > Today button, nothing happens. I've thoroughly gone &g...

Bank Balance NA in Essentials
I'm on the Essentials trial right now and my bank balance is N/A. I have an adjusted balance, but that's it. Can anyone help? I have done a Quick Repair and tried reimporting statements. Thanks! Independent of your current problem, which people here can troubleshoot if you give additional information (such as if your download is via "Third Party" or a direct download from your bank) I would strongly recommend that you give up on the Money Essentials trial and switch to the Money Deluxe trial. MEss is a totally useless program which is, at best, a downloadable chec...

Kurie Spuro thelw ena keimeno san auto na mhn moiazoun apla na kinhtai sto idio plaisio
Feugontas apo thn polh mou kai erxomenh sto Agrinio gia spoudes ta sunaisthhmata mou htan anameikta hmoun xaroumenh kai sunama stenaxwrimenh.Xaroumenh pou ksekinouse h foititikh mou zwi kai stenaxwrimenh epeidh tha afhna th polh kai tous filous mas. Apo th polh tou Agriniou de leipei tipota,exei polla merh diaskedashs, polu kalh agora me poikilia prointwn , kai oi apostaseis einai mikres.Mpainontas sto panepisthmio tou Agriniou arxika aisthanomoun abola apenanti stous kathhghtes mou, stous sumfoithtes mou logw tou oti den hksera kanenan,omws otan gnwristhkame kai auto den arghse...

Error while installing CRM 3.0 02-01-07
i recieve an error while installing the msxml 4.0 hotfix that comes with the CRM 3.0 installation steps , the error message was "This KB887606_42 is for a different hardware platform" and my operating system is windows 2003 x64 ...

I need to compare 3 numbers and find the one in the middle
I have three numbers in a single row and would like to identify the middle number enter that number in another cell. Example: 1st # 2nd # 3rd # result 628 678 720 678 655 625 700 655 748 720 725 725 is there a function in excel that can do this? Thanks, Eddie Try =LARGE(A1:C1,2) VBA Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=56811 fasteddie wrote.....

CRM 3 English to CRM 3 Greek
Hi! During tha last couple of months we developed our solution with the (available) English Version of CRM 3. Now the Greek version is out and we wonder about the easist path needed to migrate to the Greek language version (on the same server). Can we just export the customizations.xml and use the same databases? How can we export our data from CRM 3 en to CRM 3 gr - if this is not going to work? Thanking you in advance Dimitris You cannot use the export/import customization feature across different language versions. I am affraid that you will need to re-do all the customization...

Multi-Currency in 3.0
Has anyone addressed this requirement in a previous 1.2 or current 3.0 project? On 19 Dec 2005 14:05:49 -0800, "crmask" <michaelcrogers@hotmail.com> wrote: >Has anyone addressed this requirement in a previous 1.2 or current 3.0 >project? We have done for 1.2 in a simple way. For a fuller solution take a look at http://www.k3btg.com/crm/k3crm_functionality.html#currency What do you need? Julian Sharp Vigence for MS CRM in the UK See my MSCRM blog http://spaces.msn.com/members/mscrm crmask wrote: > Has anyone addressed this requirement in a previous 1.2 or curren...

error msg #3
To anyone; One of our users is trying to type a new message---in the process, Outlook displays a message saying 'trying to load Microsoft Word as your email editor' and then the pc appears to hang. Any help would be appreciated. Thanks. ...

Histograms #3
Help Me! I am trying to create a Histogram on Excel. The onl information that I have is class limits and the frequnecy's for eac class. Every time I enter the information on Excel and click o Hisogram it changes my Bins. I know there is a way to create one wit this inforamtion, but I have no idea how. Thanks -- Message posted from http://www.ExcelForum.com TarheelGirl33 - > Help Me! I am trying to create a Histogram on Excel. The only information that I have is class limits and the frequnecy's for each class. Every time I enter the information on Excel and click on Hisogram it ...

From To in Activity View in 3.0
How come From and To are not available fields when customizing the Activity Views? Or am I missing something? -- Sydne Strong The from and to fields are specific to activity types such as an email and not the base of Activity itself. To change that view, you need to change the email view for example and then when you filter the activities to show emails, it will show. "Sydne" wrote: > How come From and To are not available fields when customizing the Activity > Views? Or am I missing something? > -- > Sydne Strong > ...

Text #3
In Cell A1,2 ,3 ect i have the following info 12345BO 1234BO 123456BO How do i convert these in Cells B1, 2, 3 etc to : 012345 001234 123456 To create a 6 figure number with the BO dropped off? in column b put this formula find(a1,"BO",1) in column c put this formula mid(a1,1,b1-1) format the c column to numbers "Dava Sutts" wrote: > In Cell A1,2 ,3 ect i have the following info > > 12345BO > 1234BO > 123456BO > > How do i convert these in Cells B1, 2, 3 etc to : > 012345 > 001234 > 123456 > > To create a 6 figure number with th...

Serialization Question #3
MFC provides CArchive in support of serialization. We can save and open an object in a persistent way. Here is my furthur question: How to derive a class from CArchive and let it remember the current caret position and clipboard objects such that we get the same environment when we open the object next time. You don't derive from CArchive for this. You would derive a different class, such as CSmartEdit, from a CEdit, that would serialize the edit control and also the GetPos() values. That has nothing to do with CArchive. And it would be a Really Bad Idea to modify the clipboard withou...

feet and inches #3
i am trying to get a cell to enter 8'x9' by just entering 8x also if i enter 42-1/2x24 i want it to return 42-1/2"x24 i can find nothing on feet and inches in the help Hi have a look at http://www.cpearson.com/excel/fractional.htm -- Regards Frank Kabel Frankfurt, Germany "Brian" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:04E7D127-5759-4B8A-92C1-6D97906B805C@microsoft.com... > i am trying to get a cell to enter: > 8'x9' by just entering 8x9 > also: > if i enter 42-1/2x24 i want it to return 42-1/2"x24' > i ...

Licensing #3
Is it possible to run Exchange 2003 (65 CALS) on Server 2000 (5 CALS)? -- Gary Ainsworth ...

Vlookup returns #NA
<!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> XL2000 Vlookup returns #NA <p>on sheet1 CF16&nbsp; I am using this formula =IF(DH15="","",VLOOKUP(DH15,partlist,1)) <br>&nbsp;&nbsp; in cell DH15 I am using 35580 which is a part number from sheet named (PARTS) found in the 4th column "D" <br>on sheet named (parts) insert-named-defined partlist =parts!$A$2:$D$60 <br>I need it to return charecters in column A can someone please tell me what I forgot to do since this formula is working ok i...

Test Batch Count = 0 when importing to CRM 3.0 with SQL 2005
I'm trying to migrate some accounts (about 300) and contacts to CRM 3.0 using SQL 2005 on SBE 2003. The source is a .xls and I have mapped everything using SSIS including the Owners which i mapped later using the wizard provided in DMF, the problem is that cdf_<entity>_info tables are not being populated at all, i think that triggers are not being used by SSIS. Consenquently, when i launch the final wizard, everything results in "Test Batch Count = 0". I have read that when using DTS, you had to disable Fast Load in order to use the triggers, but is there something simila...

Items to Review #3
I downloaded the money file from my bank and it said it imported correctly. But I am unable to review the new additions. It says I have Items to Review but nothing shows up. I also tried the 'transactions to read' but it did not work either. I have money 2004 Thanks, Tom In microsoft.public.money, Skooz wrote: >I downloaded the money file from my bank and it said it imported correctly. >But I am unable to review the new additions. It says I have Items to Review >but nothing shows up. I also tried the 'transactions to read' but it did not >work either. > &...

Duplicate email #3
I have the problem with my microsoft outook, when after changing for m corporate edition to internet edition it start receive duplicate email. Do you have any Rules Wizard rules that don't end in "Stop Processing" actions? "alvin" <anonymous@discussions.microsoft.com> wrote in message news:09a201c3a1a5$cc08f240$a001280a@phx.gbl... > I have the problem with my microsoft outook, when after > changing for m corporate edition to internet edition it > start receive duplicate email. ...

Combining Several Worksheet into one #3
I have over 30 excel worksheets that are: 1. Password protected 2. The sheet is also password protected 3. Each worksheet contains only one tab call "All" and this tab is in the same format and contains the same column in every sheet. 4. It's located in the same folder I need to write a macro that will open all these workbook in this folder and combine the data into one new sheet with only one tab called "All". I am able to write the code to open all the workbook but am having a difficult time figuring out how to copy only the cells with data into the new workbook...