Help with SUMPRODUCT

[Frick]

In Sheet1:

C4:BZ4 contains Dates equal to 1st day of month
C5:BZ5 contains Dates equal to last day of month

C10:BZ20 contains both numbers and text


In Sheet2:

C8:BZ8 contains Dates

In range C10:BZ20 is where I need a SUMPRODUCT that will use the date in row
8 find the reference cells in sheet1 within the date range and return either
the reference number or reference text.

It does not have to sum since the match will either be a single number or
text. Maybe some sort of INDEX or LOOKUP would work. I'm not sure.

Thanks for any help on this.

 

[Guest]

Sounds like you want an HLOOKUP function. Details in Excel Help.

 

[Domenic]

Hi,

If I understand you correctly, try putting the following formula in cell
C9 on Sheet2, and copy across:

=INDEX(Sheet1!$C$10:$Z$10,MATCH(1,(Sheet2!C8>=Sheet1!$C$4:$Z$4)*(Sheet2!C
8<=Sheet1!$C$5:$Z$5),0))

Enter the formula using CTRL+SHIFT+ENTER.

Hope this helps!

 

[Frick]

Hi:

In Sheet1:



C4=1/1/05 D4=2/1/05 Continues out to BZ4

C5=1/31/05 D5=2/27/05 Continues out to BZ5



C11=5 D11=TEST Continues out to BZ11





In Sheet2:



D97=2/10/05 E97=8/14/05 Continues out to O97

D99 through O99 is where I need a formula.



For example: based on the date in cell D97=2/10/05 it would find the cell
reference in Sheet1 Col D and return the result "TEST".



I hope this better explains the structure.



Thanks again for any help.

 

[Domenic]

Yes, I think that better explains it. Try this then,

=INDEX(Sheet1!$C$11:$BZ$11,MATCH(1,(Sheet2!D97>=Sheet1!$C$4:$BZ$4)*(Sheet
2!D97<=Sheet1!$C$5:$BZ$5),0))

Don't forget to enter this formula using CTRL+SHIFT+ENTER.

 

[Guest]

You need a HLOOKUP() function

=VLOOKUP(D97+1,$C4:$BZ11,8)

This formula adds one day to the lookup date so that it will find the correct data on the first day of the period, it looks for the largest start date that is less than that date, then it reads down to the eighth row in the block and returns the data there.

This will work for you if your start dates are in chronological order. If they are out of order you will get unreliable results.

 

[Guest]

Sorry that should be HLOOKUP in the example (not VLOOKUP).