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...

--
HTH...

Jim Thomlinson


"Scott@CW" wrote:

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.

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


"Jim Thomlinson" wrote in message
...
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...

--
HTH...

Jim Thomlinson


"Scott@CW" wrote:

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.

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...
--
HTH...

Jim Thomlinson


"Peo Sjoblom" wrote:

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


"Jim Thomlinson" wrote in message
...
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...

--
HTH...

Jim Thomlinson


"Scott@CW" wrote:

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.

You can use

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



--


Regards,


Peo Sjoblom





"Jim Thomlinson" wrote in message
...
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...
--
HTH...

Jim Thomlinson


"Peo Sjoblom" wrote:

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


"Jim Thomlinson" wrote in
message
...
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...

--
HTH...

Jim Thomlinson


"Scott@CW" wrote:

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.