SOLVED

# need help with a formula

Occasional Contributor

# need help with a formula

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.
11 Replies

# Re: need help with a formula

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.

# Re: need help with a formula

And what result do you expect?

# Re: need help with a formula

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

# Re: need help with a formula

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.

# Re: need help with a formula

its not working. it shows #NAME?
best response confirmed by SKA217 (Occasional Contributor)
Solution

# Re: need help with a formula

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.

# Re: need help with a formula

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.

# Re: need help with a formula

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

# Re: need help with a formula

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.

# Re: need help with a formula

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.