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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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")
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308

ADVERTISEMENT

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
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
Have you understood the first answer? If you got the first answer then you could do this by yourself.
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308

ADVERTISEMENT

Yes i understood but how to take for particular column's value i,.e YTD's.
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
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
 

mehidy1437

Active Member
Joined
Nov 15, 2019
Messages
270
Office Version
  1. 2013
Platform
  1. Windows
  2. Mobile
  3. Web
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")
 

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
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")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,481
Messages
5,596,399
Members
414,063
Latest member
N_Bates

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
Top