vlookup does not work consistently

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Tim said:
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!
Click to expand...

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
 
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 (e-mail address removed)
 
Back
Top