Sumproduct with text answer

[Dickerswastic]

I am trying to do a leave booking worksheet. On sheet 1 I have employee numbers in column A and dates in row 4 ie 29/7, 30/7, etc. In sheet 2 I have a data download with employee number in column A, date of leave from in column B, date of leave to in column C and a leave type in column D.

I want to look up the employee number, date from >= row date, date to <= row date and return the leave type in the cell.

So I need a sumproduct type code. Your help would be appreciated.

Cheers

 

[indiantrix]

Without using sumproduct, this formula seems to work for me...
=IF(AND(B$4>=VLOOKUP($A5,Sheet2!$A$2:$D$19,2,0),B$4<=VLOOKUP($A5,Sheet2!$A$2:$D$19,3,0)),VLOOKUP($A5,Sheet2!$A$2:$D$19,4,0),"")
This was placed into cell B5 on Sheet 1, then copied across, then down.
Hope this is what you're seeking...
Larry.

 

[Dickerswastic]

Without using sumproduct, this formula seems to work for me...
=IF(AND(B$4>=VLOOKUP($A5,Sheet2!$A$2:$D$19,2,0),B$4<=VLOOKUP($A5,Sheet2!$A$2:$D$19,3,0)),VLOOKUP($A5,Sheet2!$A$2:$D$19,4,0),"")
This was placed into cell B5 on Sheet 1, then copied across, then down.
Hope this is what you're seeking...
Larry.

Thanks Larry, you're a legend

 

[indiantrix]

Happy to help. Signing off now. Hurricane Irene requires my utmost attention! Larry.

 

[Dickerswastic]

Happy to help. Signing off now. Hurricane Irene requires my utmost attention! Larry.

Hi Larry

Hope you are OK with Irene.

Sorry forgot to mention that the list might have more than one entry for a person. Using vlookup returns only the first line item of that person.

eg
18/8/11-19/8/11 Annual Leave
20/8/11-21/8/11 Leave without pay

So on sheet 1 I need it to differentiate.

Cheers
Nick

 

[Dickerswastic]

Hi

I have worked it out with an INDEX and MATCH function. Jeez how complicated!!!!

Cheers