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)! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP 1st Friday in April and So On
Doug:
If I am hearing your right, you have a column of dates for the past three years. For each date/row, you have more columns of information on how many people wanted appointments on that day. I am assuming the date is in column A. Insert a couple of columns after the date. In column B =weekday(a1,2) : The identifies the day of the week. Note the "2" makes Monday = 1, Tuesday = 2, etc. In column C In column C =Trunc((Day(A16)+6.9)/7) : This tells which week it is in the month You can then use your Filter to look at all the 1st Fridays of the month. Hope I was on track with what you were trying to say. Jeff "DOUG" wrote: 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)! |
Reply |
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 |