Using HLOOKUP for date values

Question

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? — Jacob Jan Tuinstra, Jul 1, 2015 at 9:16
@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

Spedwards · Accepted Answer · 2015-07-01 11:19:15Z

1

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

answered Jul 1, 2015 at 11:19

Spedwards

2151 silver badge9 bronze badges

Add a comment |

1 Answer 1