Match & Index Between Dates

[jennyaccord]

This is one of those stupid things that I thought would be simple.

I have spreadsheet comprised of 2 worksheets: Sheet 1 is a raw data sheet and contains information pulled from several different workbooks. Sheet 2 is the calculations sheet.

Sheet 1 looks something like this:

Please Login or Register  to view this content.

This is a sample of a much larger batch of data and is simply dates on which a certain piece of equipment is booked out. Merging the information from the separate sheets stacks the information in sheet 1.

Sheet 2 looks something like this:

Please Login or Register  to view this content.

And is a visual calendar of sorts.

What I need is a vlookup type formula that I can enter into each empty cell on sheet 2 that will display the word "Yes" if a piece of equipment has been booked out on the date displayed in the column header. So looking at my first example at the top, Sheet 2 would wind up looking a bit like this:

Please Login or Register  to view this content.

I know it's a Match, Index, Vlookup deely, but I can't for the life of me figure it out.

Any ideas?

[NBVC]

something like:

=IF(SUMPRODUCT(--('Sheet1'!$A$2:$A$100=$A2),--('Sheet1'!$B$2:$B$100<=B$1),--('Sheet1'!$C$2:$C$100>=B$1)),"Yes","")

adjust sheetname and ranges to suit... then copied down and across the matrix.

If you are using Excel 2007, you can use COUNTIFS instead:

=IF(COUNTIFS('Sheet1'!$A$2:$A$100,$A2,'Sheet1'!$B$2:$B$100,"<="&B$1,'Sheet1'!$C$2:$C$100,">="&B$1), "Yes","")

which is more efficient.

[jennyaccord]

Flippin' Sumproduct. I love Sumproduct. I've been messing around with Match and Index and completely forgot about Sumproduct.

Thank you.....again.