vlookup error

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

Guest

I am getting the folllowing error (#N/A) with the below formula

=VLOOKUP(F1,A20:D680,4,FALSE)

In cell F1 is this formula
='180 Report'!D6

Please help. I am trying to get the column information in column D according
to the value from F1.
 
Does the Value in F1 seem to appear in the range A20:F20.

If not then the Vlookup is working correctly. To avoid the NA in this case
try this formula...
=if(countif(A20:A680, F1) = 0, 0, VLOOKUP(F1,A20:D680,4,FALSE))

If however the value does seem to have a match then the issue is that the
match is not exact. In that case look for padded blanks at the end of text or
possibly you are trying to look up a number value from F1 while A1:A680 are
text values (or vice versa)...

If you need more help just reply back...
 
Just a heads up using COUNTIF, if the problem for instance is that F1 is a
text number and A20:A680 holds real numbers and that is the reason for the
error then COUNTIF will not work since it doesn't make any difference
between

=COUNTIF(Range,"1")

and

=COUNT(Range,1)


--


Regards,


Peo Sjoblom
 
Good point. That will do an implicit check for text vs number. If you get a
NA return value then Countif found it but Vlookup didn't so you have a data
type mismatch...
 
You can use

=IF(ISNUMBER(MATCH(F1,A20:A680,0)),VLOOKUP(F1,A20:D680,4,FALSE),0)



--


Regards,


Peo Sjoblom
 
Back
Top