Excel formula to get the value from another sheet

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
Hi Everyone,


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

ECPCPECPEVPECl
2​
RPBAS
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
See below...

Pay_Mr_Excel.xlsx
ABCDE
1ECPCPECPEVPECl
22RPBAS177800
3HAR0
4CA0
5MA0
6CCP0
7FVA0
8DSR0
9PDS0
10VOE0
11CPD0
12JB0
13LET0
14SA0
15TE0
Target_Ws
Cell Formulas
RangeFormula
D2:D15D2=IFERROR(INDEX(Source_Ws!$A$2:$AI$10,MATCH(A2,Source_Ws!$A$2:$A$10,0),MATCH(C2,Source_Ws!$A$1:$AI$1,0),1),"0")
 
Upvote 0
Hi Everyone,


Kindly check with the attachment and read the below lines.

Like the above way i am trying to get the data, if my data is in a particular column.

Like below example:

If my EC is 2 and my PEC is DSA then based on that i need to get the YTD value from the Source_Ws sheet

ECPCPECPEV
2​
DSA
12000​
LTAA
4500​
PDA
0​
VOEA
0​
6​
DSA
LTAA
PDA
VOEA
14​
DSA
LTAA
PDA
VOEA
 
Upvote 0
Have you understood the first answer? If you got the first answer then you could do this by yourself.
 
Upvote 0
I tried with the below formula but i don't understand where i am going wrong.

=IFERROR(INDEX(Source_Ws!A1:AR20,MATCH(Target_Ws!A2,Source_Ws!A1:A20,0),MATCH(Target_Ws!C2,Source_Ws!C1:C20,0),1),"0")

Could you correct me
 
Upvote 0
See below.
Rem_Mr_Excel.xlsx
ABCDE
1ECPCPECPEVPECl
22DSA12000
3LTAA0
4PDA0
5VOEA0
66DSA4500
7LTAA0
8PDA0
9VOEA0
Target_Ws
Cell Formulas
RangeFormula
D2:D9D2=IFERROR(INDEX(Source_Ws!$AR$2:$AR$20,MATCH(A2,Source_Ws!$A$2:$A$20,0),MATCH(C2,Source_Ws!$C$2:$C$20,0),1),"0")
 
Upvote 0
Can we use search for YTD and then instead of the highlighted code.

=IFERROR(INDEX(Source_Ws!$AR$2:$AR$20,MATCH(A2,Source_Ws!$A$2:$A$20,0),MATCH(C2,Source_Ws!$C$2:$C$20,0),1),"0")
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top