Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use a date in a cell for "lookup_value" in HLOOKUP functi
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". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I use a date in a cell for "lookup_value" in HLOOKUP functi
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 "joker" wrote: 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". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a "date selector box" or "pull down box" in a cell | Excel Worksheet Functions | |||
How can I use the ROUNDOWN and ROUNDUP functions in an "IF" functi | Excel Worksheet Functions | |||
which record is deleted when I use the "remove duplicates" functi. | Excel Worksheet Functions | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Multiple "lookup_value" | Excel Worksheet Functions |