How do I use a date in a cell for "lookup_value" in HLOOKUP functi

[Guest]

I am using the HLOOKUP function to grab a date in an adjacent cell as the
"lookup_value" (the first argument in the syntax for the function). The date
is in the format of "Sunday, January 1, 2006". I use the function to go to
another worksheet, look across a row, find the date, then return back the
contents of a cell below the referenced date. The problem I am having is
that the HLOOKUP function sees the date as a date in sequential serial number
form. When it tries to look for the date in the other worksheet, it does not
find it and returns "#N/A".

 

[Guest]

If I'm understanding correctly, the lookup value you have is numeric (though
formatted as a date) and the first row in the table array has dates entered
as text. (If they're both numeric or both text, the lookup should work.) If
so, then your challenge is to convert the lookup value to a text string that
matches the table. Instead of hlookup(a1, ....), you can use
hlookup(text(a1,"Dddd, Mmmm dd, yyyy"), ....), embedding that conversion
within the lookup.
If it's an option, however, I'd change the table to use real dates (that is,
numbers) formatted to read as you like. There are just more things that can
differ among nearly identical strings (extra spaces, missing commas, etc).
--Bruce