Use of index for multiple sheets and match for three criteria

[sucitanr]

---

Hi ,

I am making a format of salary slip using index and match function.
i am trying that if i put the month and emp. id the rest data should come automatically to that payslip format. but i have 12 worksheet having 12 month salary on it. and in that i have already made table of the salary made for particular month naming the table name.
now i want to use index formula for all the worksheet and match formula in such a way that if i just put the month and emp id i get the pay slip made for the particluar employee.
my every worksheet has the below header:-

emp id	name	month	lwp	salary payable
1	praveen	may'18	1	18000
2	deepak	may'18	0	9000

<tbody>
</tbody>

this is may month salary worksheet. like this i have 11 more month salary work sheet.

and i have made a format of payslip in which i have put all the details.i am trying for a formula through which if i just put month and emp id ,i get the data in the desire colums of that payslip from the desired worksheet.

Plz can u help.

 

[sucitanr]

Thanks . I tried the formula and i am able to do it now.

But can u plz explain me the formula as still i didnt understand the logic of 4 and 1 in VLOOKUP(B2,INDIRECT(REPLACE($D$1,4,1,"_")),5,0).

Thanks for the help again.

 

[Aladin Akyurek]

Thanks . I tried the formula and i am able to do it now.

Great to hear that.

But can u plz explain me the formula as still i didnt understand the logic of 4 and 1 in VLOOKUP(B2,INDIRECT(REPLACE($D$1,4,1,"_")),5,0).

Thanks for the help again.

D1 contains a value like may'18. We want to get from this may_18, that is, replace this ' token with this _ token,
because may_18 is a name we can feed INDIRECT with to obtain the table corresponding to that month.

The position of the ' token in may'18 is 4. REPLACE is asked to insert the _ token whose length is 1. SUBSTITUTE(D1,"'","_") would here effect the same result.

 

[sucitanr]

Thanks alot .. You are an expert...

I really troubled u alot..

Thanks again.

 

[Aladin Akyurek]

Thanks alot .. You are an expert...

I really troubled u alot..

Thanks again.

You are welcome.