Forum Discussion

Ryan Wiggam's avatar
Ryan Wiggam
Copper Contributor
Feb 20, 2018

VLOOKUP #N/A Error

Using VLOOKUP to match Date & Time (format dd/mm/yyyy hh:mm) to another date and time (Column A) in the same format and return a value in Column B.

 

Formula is returning a Value Not Available Error in a number of cases, when the value definitely DOES exist.  I have also checked for additional spaces, etc. with no luck.

 

Does anyone know of another reason this error might be occurring?

 

Thanks

excel
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Feb 20, 2018

    Hi,

     

    Maybe the dates in one of the date columns are not real dates, it's may be treated as texts!

    Please provide us with a sample of these dates in an attached workbook!

    • Ryan Wiggam's avatar
      Ryan Wiggam
      Copper Contributor
      Feb 20, 2018

      Hi Haytham,

       

      Thanks for the response.

       

      Yes, I checked that and they are definitely dates.  Exactly the same Custom formatting.

       

      I will attached one when I get a chance.

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Feb 20, 2018

    Hi Ryan,

     

    As variant it could be rounding. Did you try to compare such cells like

    =(Avalue=Bvalue)

    if it returns TRUE of FALSE?

    • Ryan Wiggam's avatar
      Ryan Wiggam
      Copper Contributor
      Feb 20, 2018

      Hi Sergei,

       

      Thanks for the response.  I hadn't thought to try that, I checked manually and the values seem to match.

       

      Just tried your method and it returns TRUE.

Resources