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.

 

[Aladin Akyurek]

We have thus...

Book1
	A	B	C	D	E
1	id	name	month	LWP	Salary Payable
2	1	Prasun	May'18	1	12000
3	2	Alok	May'18	0	10000
4	3	Rahul	May'18	0	9000
5	4	Deepak	May'18	0	8000
May'18

Book1
	A	B	C	D	E
1	id	name	month	LWP	Salary Payable
2	1	Prasun	Jun'18	1	12000
3	2	Alok	Jun'18	0	10000
4	3	Damon	Jun'18	0	20000
5	4	Deepak	Jun'18	0	8000
Jun'18

Given the forgoing sheets, care to fill in the expected results in the following sheet?

Book1
	A	B	C	D
1	COMPANY NAME		MONTH	MAY'18
2	EMP ID	1
3	NAME
4	LWP
5	Salary Payable
6	TOTAL
Sheet3

 

[sucitanr]

yes exactly this is the same sheet design.

I just want to put a formula in the name, lwp and salary payable column in sheet 3, so that if i just fill month and emp id column , i get the data for rest of the columns from the desired sheet.

can u help me with the full formula.

in my previous message , i have wrote the formula that i tried , but it in not for all worksheet.

Plz let me know the full formula on this basis .

 

[Aladin Akyurek]

yes exactly this is the same sheet design.

I just want to put a formula in the name, lwp and salary payable column in sheet 3, so that if i just fill month and emp id column , i get the data for rest of the columns from the desired sheet.

[...]

I'm asking you what the expected values would be? For example, is LWP 1,000,000?

 

[sucitanr]

I guess you didnt get my point .

ok let me tell u on the basis of these sheets.

I want that if in sheet 3 i put month as may'18 and employee id as 1, so with the formula in the columns ,the LWP column should pick the data from May'18 sheet which will be 1. And name column will pick name as prasun and salary payable column will pick 12000 form May'18 sheet.

I have already made the May'18 and Jun'18 sheet.I dont want any calculations.

I just want to fill the data from May'18 and Jun'18 sheets , to make the individual employee salary slip . And so i only need formula to pick the data .

i hope i am clear now.

	A	B	C	D
1	COMPANY NAME		MONTH	MAY'18
2	EMP ID	1
3	NAME	i want to put formula here
4	LWP	i want to put formula here
5	Salary Payable	i want to put formula here
6	TOTAL

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

 

[Aladin Akyurek]

COMPANY NAME is unknown (no problem)
MONTH is MAY'18 (This is given.)

EMP ID is 1 (This also given.)

NAME is Prasun (A formula must fetch this name.)

LWP is 1 or 2 (A formula must compute this. Since MONTH = May'18, it must be 1, right?)

Salary Payable is 12000 or 24000 (A formula must deliver this. Sine MONTH = May'18, it must be 12000, right?)

TOTAL is what?

 

[sucitanr]

yes formula will take all data as it is from the sheets.

COMPANY NAME is unknown (no problem)
MONTH is MAY'18 (This is given.)

EMP ID is 1 (This also given.)

NAME is Prasun (A formula must fetch this name.) - yes formula will just pick this data.

LWP is 1 or 2 (A formula must compute this. Since MONTH = May'18, it must be 1, right?) - yes formula must deliver this which will be 1.

Salary Payable is 12000 or 24000 (A formula must deliver this. Sine MONTH = May'18, it must be 12000, right?) - yes formula must deliver this which will be 12000.

TOTAL is what? - you can ignore total, as it is just a detail in this sheet only.

i also made the formula that i made but it was only picking the data from one sheet only considering one match only ie emp id.you can see this formula .

	A	B	C	D
1	COMPANY NAME		MONTH
2	EMP ID	1
3	NAME	=INDEX('May ''18'!$B$2:$E$5,MATCH($A$2,'May ''18'!$A$2:$A$5,0),MATCH(A3,'May ''18'!$B$1:$E1
4	LWP	=INDEX('May ''18'!$B$2:$E$5,MATCH($A$2,'May ''18'!$A$2:$A$5,0),MATCH(A4,'May ''18'!$B$2:$E1
5	Salary Payable	=INDEX('May ''18'!$B$2:$E$5,MATCH($A$2,'May ''18'!$A$2:$A$5,0),MATCH(A5,'May ''18'!$B$2:$E1
6	TOTAL

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

LIKE THIS I MADE A FORMULA FOR THESE THREE COLUMN, BUT WITH THIS I AM ONLY ABLE TO TAKE FROM ONE WORKSHEET AND USING ONLY ONE MATCH,WHICH IS NOT HELPFUL.

I TRIED TO PUT "INDIRECT" ALSO IN THIS FORMULA WITH TWO MATCH FUNCTIONS BUT I WAS NOT ABLE TO DO SO.

SO I ONLY WANT SOME KIND OF FORMULA , IN WHICH IF I PUT EMP ID AND MONTH , AUTOMATICALLY THE FORMULA SHOULD SPOOL THE DATA FROM THE RESPECTIVE SHEETS.

 

[Aladin Akyurek]

A1:E5 of May'18 is named may_18 via the Name Box.
A1:E5 of Jun"18 is named june_18 via the Name Box.

In B3 of Sheet3 enter:

VLOOKUP(B2,INDIRECT(REPLACE($D$1,4,1,"_")),2,0)

In B4 of Sheet3 enter:

=VLOOKUP(B2,INDIRECT(REPLACE($D$1,4,1,"_")),4,0)

In B5 of Sheet3 enter:

=VLOOKUP(B2,INDIRECT(REPLACE($D$1,4,1,"_")),5,0)

 

[sucitanr]

Thanku so much ,,it really helped ... thanks again..

 

[sucitanr]

Hi i tried in my original sheet , but i am not able to do so.

I am not able to undertand = VLOOKUP(B2,INDIRECT(REPLACE($D$1,4,1,"_")),5,0) - what 4 stand for here?

I tell u, i have 12 month sheets.

I have name them A8:S19 of May'18 is named may_18 via the Name Box.
A8:S19 of jun'18 is named jun_18 and the last sheet as A8:S19 of apr'18 is named apr_19.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #0057d6}span.s2 {color: #006107}span.s3 {color: #ab30d6}</style>I put the formula like this - VLOOKUP(F10,INDIRECT(REPLACE($F$8,18,1,"_")),2,0) ... but it didnt worked....

F10 is employee ID column and F8 is Month column.

name column is D12 and payable salary column is D17.

Can u plzzzz tell me the exact formula i should keep in name and payable column , as i tried the above but i didnt get nor i understand.

can u write the formula ..

thanku

 

[Aladin Akyurek]

You are given what you need.

Each sheet must be structured in the same way as May'18 and Jun'18. The expected month names are: Jan'18, Feb'18, Mar'18, Apr'18, May'18, Jun'18, Jul'18, Aug'18, Sep'18, Oct'18, Nov'18, and Dec'18.

The expected fields in each sheet are: id, name, month, LWP, and Salary Payable.

Good luck.