Excel formula to get the value from another sheet

[GirishDhruva]

Hi Everyone,

Pay_Mr_Excel.xlsx

Shared with Dropbox

www.dropbox.com

Check with the above attachment and kindly read the below lines.

In column "D" of Target_Ws i am trying to add the formula, where i need to get the value of that EC based on PEC.

Like below:

If my EC value is 2 based on that i need to get the value of BAS from the Source_Ws sheet and in the similar way i need to get all the other values based on the EC and PEC

EC	PC	PEC	PEV	PECl
2​	RP	BAS	177800​
		HAR	88900​
		CA	0​
		MA	0​
		CCP	0​
		FVA	0​
		DSR	0​
		PDS	2200​
		VOE	0​
		CPD	0​
		JB	0​
		LET	0​
		SA	0​
		TE	0​
		PFEC	173216​
		VPFC	442116​
		IT	17780​
		PRT	0​
		IS	117430​
		IS Ar	200​
		FCD	502​
		TD	0​
		NP	2200​

Regards,
Dhruva.

 

[GirishDhruva]

Are you getting the other values like LTAA ,PDA ,VOEA values.

I am not getting the values for the if i extend my EC Value also.

EC	PC	PEC	PEV
2​		DSA	12000​
2​		LTAA	0
2​		PDA	0
2​		VOEA	0
6​		DSA	4500​
6​		LTAA	0
6​		PDA	0
6​		VOEA	0

 

[mehidy1437]

Hi I'm sorry, that's not correct.

Here below two solution.

01. With array formula, after writing the formula you have to press CRTL+SHIFT+ENTER & then drag down for others cells.

Rem_Mr_Excel.xlsx
	A	B	C	D
1	EC	PC	PEC	PEV
2	2		DSA	12000
3	2		LTAA	4500
4	26		PDA	37500
5	26		VOEA	12000
6	6		DSA	4500
7			LTAA	0
8			PDA	0
9			VOEA	0
10	14		DSA	4500
11			LTAA	0
12			PDA	0
Target_Ws

Cell Formulas
Range	Formula
D2:D12	D2	=IFERROR(INDEX(Source_Ws!$AR$2:$AR$20,MATCH(Target_Ws!A2&Target_Ws!C2,Source_Ws!$A$2:$A$20&Source_Ws!$C$2:$C$20,0),1),"0")
Press CTRL+SHIFT+ENTER to enter array formulas.

02. Second solution, add a helper column in Target_WS, between PEC & PEV, type =A2&C2 & drag down.
And on Source_WS add another column after "PM"/C, type =A2&C2 & drag down.
And at the Target_WS sheet writes the formula as below.
=IFERROR(INDEX('Source_Ws (2)'!AS2:AS20,MATCH('Target_Ws (2)'!D2,'Source_Ws (2)'!D2:D20,0),1),"0")

Rem_Mr_Excel.xlsx
	A	B	C	D	E
1	EC	PC	PEC		PEV
2	2		DSA	2DSA	12000
3	2		LTAA	2LTAA	4500
4	26		PDA	26PDA	37500
5	26		VOEA	26VOEA	12000
6	6		DSA	6DSA	0
7			LTAA	LTAA	0
8			PDA	PDA	0
9			VOEA	VOEA	0
10	14		DSA	14DSA	0
11			LTAA	LTAA	0
Target_Ws (2)

Cell Formulas
Range	Formula
D2:D11	D2	=A2&C2
E2:E11	E2	=IFERROR(INDEX('Source_Ws (2)'!AS2:AS20,MATCH('Target_Ws (2)'!D2,'Source_Ws (2)'!D2:D20,0),1),"0")

 

[GirishDhruva]

Hi @mehidy1437 worked perfectly and sorry for the late reply.

Thanks for the help and support.

 

[mehidy1437]

You are welcome.