VLOOKUP 1st Friday in April and So On

[DOUG]

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)!

 

[Jeff]

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