You're welcome. Thanks for the feedback!

Biff

"ben simpson" wrote in message
...
Excellent job Biff. I really appreeciate the help that you gave
me.....Ben

"Biff" wrote:

If anybody is still following this thread........

The OP contacted me via email and provided a complete description of his
file layout and data.

The problem was in our (the respondents) interpretation of his post. The
one
range of dates posted as being in F51:AF52 was actually a single row, row
51, but the two rows were merged! F51 was the start of the pay period and
AF51 was the end of the pay period.

The resulting formula was:

=INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data
Sheet'!A$4:A$103=C$51)*('Data
Sheet'!A$4:A$103<=AF$51),ROW('Data Sheet'!A$4:A$103)-ROW('Data
Sheet'!A$4)+1),ROWS($1:1)))

Used CF to "hide" any errors.

Biff

"Biff" wrote in message
...
#NUM! would mean there are no matches.

Send me an email..........if you can't send the file, just make a list
of
these entries:

I need to know *EXACTLY* what you have in A4:A103, B4:B103, F51:AF51,
F52:AF52, C51. Don't just tell me that you have dates! I need to know
WHAT
DATES!!!!!!!!!!

My address is: xlcanhelpatcomcastperiodnet

Remove "can" and change the obvious.

Biff

"ben simpson" wrote in message
...
I got the areas fixed that you pointed out. Now I get a #NUM error. i
need
guidance from here (obviously...hehehe). This is what it looks like.
In an array of course.

=INDEX('Data Sheet'!B$4:B$103,SMALL(IF(('Data
Sheet'!A$4:A$103=INDEX(F$51:AF$51,MATCH(C$51,F$51 :AF$51,1)))*('Data
Sheet'!A$4:A$103<=INDEX(F$52:AF$52,MATCH(C$51,F$51 :AF$51,1))),ROW('Data
Sheet'!A$4:A$103)-ROW('Data Sheet'!A$4)+1),ROWS($1:1)))

Where does it go from here?.....Thanks....Ben

"Biff" wrote:

This portion:

<=INDEX(F$51:AF$52,MATCH(C$51,F$51:AF$51,1)))

Should be:

<=INDEX(F$52:AF$52,MATCH(C$51,F$51:AF$51,1)))

Also, since there may be more than one match, when you copy the
formula
down
you don't want these ranges to increment, do you?

'Data Sheet'!B4:B103
'Data Sheet'!A4:A103

So, make the row references absolute:

'Data Sheet'!B$4:B$103
'Data Sheet'!A$4:A$103

I have no clue as to what you meant by error trap the formula. Can
you
clarify that for me?

When you got the #REF! error, an error trap is a portion of the
formula
that
"anticipates" when errors will be generated and prevents them from
being
displayed. Let's get the basic formula working first then we'll deal
with
errors later!

Biff

"ben simpson" wrote in message
...
Yikes...you weren't kidding. I copied the formula down, and pasted
it
in.
I
had to change the cell references to the correct sheet (sorry if I
wasn't
clear). Now it giving me a #REF error, and I don't know why.
Here's
what
i
have now:
As an array.

=INDEX('Data Sheet'!B4:B103,SMALL(IF(('Data
Sheet'!A4:A103=INDEX(F$51:AF$51,MATCH(C$51,F$51:A F$51,1)))*('Data
Sheet'!A4:A103<=INDEX(F$51:AF$52,MATCH(C$51,F$51:A F$51,1))),ROW('Data
Sheet'!A4:A103)-ROW('Data Sheet'!A4)+1),ROWS($1:1)))

I have no clue as to what you meant by error trap the formula. Can
you
clarify that for me?.....Thanks....Ben





"Biff" wrote:

I told you this would be complicated!

I'm assuming that:

row 51 = start of period
row 52 = end of period

Try this:

Entered as an array using the key combo of CTRL,SHIF,ENTER:

=INDEX(B$4:B$103,SMALL(IF((A$4:A$103=INDEX(F$51:A F$51,MATCH(C$51,F$51:AF$51,1)))*(A$4:A$103<=INDEX( F$52:AF$52,MATCH(C$51,F$51:AF$51,1))),ROW(A$4:A$10 3)-ROW(A$4)+1),ROWS($1:1)))

Copy down until you get #NUM! errors. If you want an error trap
the
formula
will be twice as long!!!!!!!!

Biff

"ben simpson" wrote in
message
...
Hi there.

I've been working on is a time sheet (and almost done with it).
During
our work periods throught the year we accrue holidays (just like
vacation
time).
What I'm wanting to do is compare the present work period
dates
(I
have
the dates in ms date format in F51:AF52), to a list that I
manually
have
looked up of the holiday dates (this is in the ms date format in
A4:A103
on
the data sheet). In cell C28 on the time sheet, I need the name
of
the
holiday and/or holidays that have accrued during the pay period
(thats
in
the
cell next to dates in B4:B103 on the data sheet). Hope I haven't
lost
you
yet.
What I have:

Data Sheet

38718 New Years Day
38733 Martin Luther King Jr. Day
38768 Presidents Day
38866 Memorial Day
38902 Fourth of July
38964 Labor Day
39044 Thanksgiving Day
39045 Day after Thanksgiving Day
39075 Christmas Eve
39076 Christmas Day


What I've been using:

=IF(ISNA(MATCH(F51:AF52,'Data
Sheet'!A4:A103,0)),"",VLOOKUP(C51,'Data
Sheet'!A4:B103,2))
This only lists the last match that it finds, and not all
the
matches,
which
is what I actually need.


What I need (in C28) if the pay period was from 12/26/2005 thru
01/21,2006:

Accrued Holiday(s) For: New Years Day, Martin Luther King Jr.

I think this is possible, I'm not savvy enough in Excel to
figure
this
out
on my own. This is way over my head. Any help is appreciated.
Thanks....Ben