Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP 1st Friday in April and So On
Formula: =VLOOKUP(A2,DatesList!$A$2:$F$386,6,FALSE) returns #N/A. The
Lookup Value = 1st Friday in April. The Table Array is the DateList in another spreadsheet. The Col_index_num is 6 and the Range_Lookup is set to "False". Both the Lookup Value column and Column 6 are formatted as TEXT. I used the Text to Columns feature to ensure both columns REALLY were formatted at Text. I have tried formatting both columns as General. I have used TRIM to remove leading and trailing spaces. I have used LENB to make sure the number of characters matched, and they do. I have tried replacing the mixed number and text expressions which start with "1st, 2nd and so on" with "First, Second and so on" - (that did not work either, so I changed them back). This frustrating roadblock began as a project where I tried to identify and average the patient demand for appointments for the last three years. A simpler way would have been to use a function to identify dates as "the 1st Friday in XXX Month" (if such a function exists), so that I could sort and average the demand for appointments on any given day. Instead, I converted the dates to text and used the "Text to Columns" feature and the "Concatenate" formula to parse out, sort and average the demand for each working day of the year. However, now I am stuck with the demand totals (and the column "1st Friday in April, for example) in one spreadsheet and the dates which will correspond to the "1st Friday in April" in another. I have tried to use VLOOKUP to draw them together, so that I may sort by dates going forward. That is the point of the exercise. Suggestions are welcome, (at least most of them)! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
April 2006 - Excel User Conference | Excel Discussion (Misc queries) | |||
Excel User Conference - April 2006 | Excel Discussion (Misc queries) | |||
Date Format - April error | Excel Discussion (Misc queries) | |||
April 1 | Excel Worksheet Functions | |||
Years when 1 April occurs on a Saturday | Excel Worksheet Functions |