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 '15 at 7:46
  • @JPV Formatted as a date. I also tried TEXT(NOW(), "M/d/yyyy") as the search key though. – Spedwards Jul 1 '15 at 7:51
  • Why don't you share an example document with us, so we can have a look? – Jacob Jan Tuinstra Jul 1 '15 at 9:16
  • @JacobJanTuinstra Added it to the bottom of the question. – Spedwards Jul 1 '15 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 '15 at 11:26

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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