I'm trying to get the HLOOKUP function working in my sheet but it's not working like it should.

The function is:

=HLOOKUP(TEXT(NOW(), "yyyy-MM-dd"), INDIRECT(TEXT(NOW(), "MMMM") & "!B1:AE5"), 3, FALSE) 

Translating the functions, that would be:

=HLOOKUP("2015-07-01", July!B1:AE5, 3, FALSE)

I get an error however:

Did not find value '2015-07-01' in HLOOKUP evaluation

That doesn't make sense since '2015-07-01' is in cell B1 in the sheet, July.
Why isn't it working and what can I do to fix it?


  • See if the value you look up (in July!B1:AE5) is formatted as text or date ?
    – JPV
    Jul 1, 2015 at 7:46
  • @JPV Formatted as a date. I also tried TEXT(NOW(), "M/d/yyyy") as the search key though.
    – Spedwards
    Jul 1, 2015 at 7:51
  • Why don't you share an example document with us, so we can have a look? Jul 1, 2015 at 9:16
  • @JacobJanTuinstra Added it to the bottom of the question.
    – Spedwards
    Jul 1, 2015 at 11:14
  • @Spedwards: you are looking for a text value, but what needs to be found is in a date-format. Can you share an example sheet ?
    – JPV
    Jul 1, 2015 at 11:26

1 Answer 1


I figured it out by changing the Search Criteria from TEXT(NOW(), "yyyy-MM-dd") to TODAY()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.