|
vlookup error
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. |
vlookup error
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. |
vlookup error
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. |
vlookup error
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. |
vlookup error
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. |
| All times are GMT +1. The time now is 10:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com