need help with a formula

[SKA217]

1. I have a excel sheet that has an employee's Provident fun deduction amount per month from the starting date
2. Now I need to bring “from which month and year an employee has started to deduct his Provident fund amount” in another summary sheet from that data series.
3. Please help me with the formula

[#2]

Attach a sample file then we could suggest formula for you. I assume VLOOKUP, INDEX/MATCH, XLOOKUP, FILTER all of these function can give you desired result.

[#3]

attached the excel file. please help me bring the starting date with formula for each employee

[#4]

And what result do you expect?

[#5]

I need the starting date (from when the employee is deduction provident fund amount)
example: Hemayet uddin from Jan, 2015
Md Moniruzzaman (Monir) from September, 2015

[#6]

@SKA217 

In a cell in row 3:

=LET(i, MATCH(TRUE,D3:AM3>0,0), m, INDEX($D$2:$AM$2,i), y, INDEX($D$1:$AM$1,QUOTIENT(i-1,12)*12+1), m &", "&y)

Fill down.

[#7]

its not working. it shows #NAME?

[#8 · Accepted solution]

@SKA217 

The formula that I posted will only work in Excel 365 / 2021. The following version should work in older versions too:

=INDEX($D$2:$AM$2,MATCH(TRUE,D3:AM3>0,0)) & ", " & INDEX($D$1:$AM$1,QUOTIENT(MATCH(TRUE,D3:AM3>0,0)-1,12)*12+1)

Confirm with Ctrl+Shift+Enter.

[#9]

Do not know how to thank you. Its working. Man you are awesome. thanks a lot. Now i need understand the formula so that i can do it myself in future.

[#10]

Brother,
Is there any way you can can explain the function to me?

[#11]

@SKA217 

The first part INDEX($D$2:$AM$2,MATCH(TRUE,D3:AM3>0,0)) looks up the month.

D3:AM3>0 returns an array of TRUE/FALSE values: TRUE for each cell in D3:AM3 with a value > 0, and FALSE for each cell that contains 0.

MATCH(TRUE,D3:AM3>0,0) searches for TRUE and returns the position of the first TRUE value in this array. That corresponds to the first month with a value > 0.

INDEX($D$2:$AM$2,MATCH(TRUE,D3:AM3>0,0)) looks up the name of that month in D2:AM2. We use the absolute reference $D$2:$AM$2 to ensure that it will remain the same when we fill down the formula.

The second part INDEX($D$1:$AM$1,QUOTIENT(MATCH(TRUE,D3:AM3>0,0)-1,12)*12+1) uses the same idea to find the year, but it is more complicated because the year appears in intervals of 12 cells in D1:AM1.

MATCH(TRUE,D3:AM3>0,0) once again returns the position of the first value > 0, i.e. the first month with a value > 0.

The year occurs in the first cell of each set of 12 cells, so we check how many times 12 goes into the position returned by MATCH, then multiply by 12, so we get 1 or 13 or 25 etc. INDEX then looks up the year in $D$1:$AM$1.

Finally, the month and year are concatenated with ", " in between.

[#12]

thanks a lot i was confused with this part QUOTIENT(MATCH(TRUE,D3:AM3>0,0)-1,12)*12+1) . Now its clear, thanks a lot for your kind help.