Forum Discussion
Excel formula help - vertical and horizontal in same calculation
Hello,
I have a set of data that contains resource names and their FTE effort per month. I'm trying to calculate their start month and end month in two cells but can't get my head around the correct formula. Here is an an example data set:
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |
| 1 | ID | Employee | Start Month | End Month | M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10 | M11 | M12 |
| 2 | 1 | John | ?? | ?? | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0.5 | 0.5 | 0 | 0 | 0 |
| 3 | 2 | Brian | ?? | ?? | 1 | 1 | 1 | 1 | 0.5 | 0.5 | 0 | 0 | 0 | 0 | 0 | 0 |
| 4 | 3 | Alison | ?? | ?? | 0 | 0 | 0 | 0 | 0 | 0 | 0.5 | 0.5 | 1 | 1 | 1 | 1 |
The result I'm looking for is for John - Start M4, End M9; Brian - start M1, End M6; Alison - start M7, End M12 .. i.e. its linked to non-zero cells
I have a formula using match/index that I found on the web that is getting the start date (example of John's row):
=INDEX('!E$1:P$1, MATCH(TRUE, '!E2:P2<>0,0))
I can't however amend that formula to work for the end date. I'm more familiar with XLOOKUP than MATCH/INDEX and I'm assuming there is some embedded XLOOKUP (=XLOOKUP("1", A:A, XLOOKUP("<>0", <<second row>>, <<first row>>) - cant get this to work , that could work here, but its outside the limits of my abilities.
Suggestions please?
As variant
Start
=XLOOKUP(2,1/(E2:P2<>0),$E$1:$P$1,,-1)End
=LOOKUP(2, 1/(E2:P2<>0), $E$1:$P$1)
4 Replies
thank you both - that works for me now! Off to audit the 3000 lines of data for accuracy...
Another alternative for Start
=TAKE(FILTER($E$1:$P$1,E2:P2),,1)for End
=TAKE(FILTER($E$1:$P$1,E2:P2),,-1)For the spill of both
=TAKE(FILTER($E$1:$P$1,E2:P2),,{1,-1})(similar with XLOOKUP)
Alternatively for End date:
=XLOOKUP(TRUE,E2:P2>0,$E$1:$P$1,,,-1)
XLOOKUP can search from last to first (6th argument of the function).
XLOOKUP function - Microsoft Support
As variant
Start
=XLOOKUP(2,1/(E2:P2<>0),$E$1:$P$1,,-1)End
=LOOKUP(2, 1/(E2:P2<>0), $E$1:$P$1)
