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:-

[TABLE="class: gmail-wysiwyg_dashes, width: 300"]
<tbody>[TR]
[TD]emp id[/TD]
[TD]name[/TD]
[TD]month[/TD]
[TD]lwp[/TD]
[TD]salary payable[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]praveen[/TD]
[TD]may'18[/TD]
[TD]1[/TD]
[TD]18000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]deepak[/TD]
[TD]may'18[/TD]
[TD]0[/TD]
[TD]9000[/TD]
[/TR]
</tbody>[/TABLE]


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.