Help!

vlookup does not work consistently

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Protection Fault Errors RSS
Next:  Plot area settings...  
Author Message
Tim Parsonson
External


Since: Aug 29, 2006
Posts: 1



PostPosted: Tue Aug 29, 2006 5:33 am    Post subject: vlookup does not work consistently
Archived from groups: microsoft>public>excel>crashesgpfs (more info?)

sometimes it returns #N/A for no reason.

e.g. see this formula
=IF(COUNTIF('competitor guideline
rates'!$C$16:$C$29,C12)=0,0,VLOOKUP(C12,'competitor guideline
rates'!$C$16:$D$29,2))

If you read it carefully, there is no way #N/A could be returned. In this
case, COUNTIF finds the argument in C12 in the array, but VLOOKUP does not!
Back to top
Franz Verga
External


Since: Jun 21, 2006
Posts: 327



PostPosted: Tue Aug 29, 2006 3:24 pm    Post subject: Re: vlookup does not work consistently [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Tim Parsonson wrote:
> sometimes it returns #N/A for no reason.
>
> e.g. see this formula
> =IF(COUNTIF('competitor guideline
> rates'!$C$16:$C$29,C12)=0,0,VLOOKUP(C12,'competitor guideline
> rates'!$C$16:$D$29,2))
>
> If you read it carefully, there is no way #N/A could be returned. In
> this case, COUNTIF finds the argument in C12 in the array, but
> VLOOKUP does not!

Hi Tim,

try ths way:

=IF(COUNTIF('competitor guideline
rates'!$C$16:$C$29,C12)=0,0,VLOOKUP(C12,'competitor guideline
rates'!$C$16:$D$29,2,0))

If your list isn't in order you need the fourth parameter of VLOOKUP set to
0 or FALSE...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
Back to top
Susan Carrier
External


Since: Sep 07, 2006
Posts: 5



PostPosted: Thu Sep 07, 2006 3:06 pm    Post subject: Re: vlookup does not work consistently [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

You might have a problem with conflicting formats. For instance, VLOOKUP will
not find a match between two cells if one is formatted as general and the
other is text. But COUNTIF is not as picky and will even find a match between
"0001" and "1" which VLOOKUP will not.

PS: once you change the format on a group of cells, you need to click F2 on
each cell to get Excel to recognize the new format. Alternatively, you can
use the TextToColumns feature which forces Excel to recognize the new format.

I teach a college course in Excel and VLOOKUP is one of the toughest
functions to troubleshoot. I have designed a handout with some hints if you
would like one....email me at scarrier@msjc.edu

"Franz Verga" wrote:

> Tim Parsonson wrote:
> > sometimes it returns #N/A for no reason.
> >
> > e.g. see this formula
> > =IF(COUNTIF('competitor guideline
> > rates'!$C$16:$C$29,C12)=0,0,VLOOKUP(C12,'competitor guideline
> > rates'!$C$16:$D$29,2))
> >
> > If you read it carefully, there is no way #N/A could be returned. In
> > this case, COUNTIF finds the argument in C12 in the array, but
> > VLOOKUP does not!
>
> Hi Tim,
>
> try ths way:
>
> =IF(COUNTIF('competitor guideline
> rates'!$C$16:$C$29,C12)=0,0,VLOOKUP(C12,'competitor guideline
> rates'!$C$16:$D$29,2,0))
>
> If your list isn't in order you need the fourth parameter of VLOOKUP set to
> 0 or FALSE...
>
>
> --
> Hope I helped you.
>
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> General Protection Fault Errors All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum