VLookup between dates, in combination with sumproduct

0
votes

This is the spreadsheet in question.

I need to calculate the FTEs (employee full time equivalents) for multiple projects, over time. Employees keep changing number of week-hours over time (sheet "Employees Import"), with maximum week hours is 40. So someone working 40 hrs a week has a FTE of 1.

I'm trying to write a formula that would fill the yellow cells. I don't think it's possible to be achieved using array formulas, so I don't have a problem with copying the formula for each week and each project. But the challenge for me is to do the sumproduct operation that includes a VLOOKUP with Employee match AND date between match at the same time. Maybe someone can help out.

EDIT: I've had a bit success with this formula:

=SUMPRODUCT({K19:K21}; ARRAYFORMULA(VLOOKUP($F19:$F21;    SORT(   FILTER({'Employees Import'!$A$1:$A\'Employees Import'!$E$1:$E\'Employees Import'!$F$1:$F\'Employees Import'!$G$1:$G}; ('Employees Import'!$F$1:$F<K$4)*(  IF('Employees Import'!$G$1:$G=""; DATE(2030;12;31); 'Employees Import'!$G$1:$G)    >K$4) ) )     ; 2)))/40
2
google-sheetsgoogle-sheets-formula
Isn't the formula you just shared doing what you want to accomplish? So your problem is solved? – Iamblichus
yeah, seems like it. I'm still testing it before I tag it as solved. – Mara

2 Answers

1
votes

This formula seems to be working:

=SUMPRODUCT({K19:K21}; ARRAYFORMULA(VLOOKUP($F19:$F21; SORT( FILTER({'Employees Import'!$A$1:$A\'Employees Import'!$E$1:$E\'Employees Import'!$F$1:$F\'Employees Import'!$G$1:$G}; ('Employees Import'!$F$1:$F<K$4)*( IF('Employees Import'!$G$1:$G=""; DATE(2030;12;31); 'Employees Import'!$G$1:$G) >K$4) ) ) ; 2)))/40

0
votes

But again you need a unique value, the one which will act like a primary key for sure not the date probably the man number

Below is a simple formula

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

I hope this will help