Using formula how to extract data depending on two values

[sprakash1704]

Dear Experts!

Relatively I am new to this forum, when I say relatively, I have been using this forum just to search and get answers for all my excel related queries for almost 6 years now and haven't posted anything or even registered. Thanks to all the experts who have helped me so for!

The question I have here is based on the table provided below. The data and headers in the column is fluctuating as this is a pivot, I will need to refer the pivot and will be extracting the required data based on "Emp ID". Need Help in doing this by formula, as I am not coding expert and I love working with formulas.

Experts I require this quite urgently, so please help!!!!!



[TABLE="width: 590"]
<colgroup><col><col span="9"></colgroup><tbody>[TR]
[TD]Emp ID[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[/TR]
[TR]
[TD]22705[/TD]
[TD]4888.47[/TD]
[TD] [/TD]
[TD]195.3[/TD]
[TD]5170.18[/TD]
[TD]5635.55[/TD]
[TD]5263.22[/TD]
[TD]6038.22[/TD]
[TD]4787.53[/TD]
[TD]5081.35[/TD]
[/TR]
[TR]
[TD]31799[/TD]
[TD] [/TD]
[TD]5487.5[/TD]
[TD] [/TD]
[TD]1388.69[/TD]
[TD]1314.11[/TD]
[TD]1182.74[/TD]
[TD]1311.87[/TD]
[TD]712.1[/TD]
[TD]1807.24[/TD]
[/TR]
[TR]
[TD]39974[/TD]
[TD]4063.28[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]6090.46[/TD]
[TD]4595.35[/TD]
[TD]7673.24[/TD]
[TD]7280.15[/TD]
[TD]3753.64[/TD]
[TD]4459.22[/TD]
[/TR]
[TR]
[TD]57683[/TD]
[TD]4212.66[/TD]
[TD]159.42[/TD]
[TD] [/TD]
[TD]4363.84[/TD]
[TD]4372.54[/TD]
[TD]4251.25[/TD]
[TD]5323.53[/TD]
[TD]4084.27[/TD]
[TD]4063.28[/TD]
[/TR]
[TR]
[TD]57794[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5569.82[/TD]
[TD]5751.9[/TD]
[TD]5696.69[/TD]
[TD]6038.89[/TD]
[TD]4378.91[/TD]
[TD]4467.81[/TD]
[/TR]
[TR]
[TD]61546[/TD]
[TD] [/TD]
[TD]40.19[/TD]
[TD] [/TD]
[TD]4733.94[/TD]
[TD]4664.96[/TD]
[TD]3729.11[/TD]
[TD]5026.33[/TD]
[TD]4609.51[/TD]
[TD]5487.5[/TD]
[/TR]
[TR]
[TD]61547[/TD]
[TD]2435.05[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5086.47[/TD]
[TD]5191.55[/TD]
[TD]4810.99[/TD]
[TD]3932.63[/TD]
[TD]4715.96[/TD]
[TD]4212.66[/TD]
[/TR]
[TR]
[TD]66751[/TD]
[TD]4467.81[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]5979.45[/TD]
[TD]5475.14[/TD]
[TD]2220.97[/TD]
[TD]5752.75[/TD]
[TD]3794.56[/TD]
[TD]4888.47[/TD]
[/TR]
[TR]
[TD]71060[/TD]
[TD] [/TD]
[TD]5512.42[/TD]
[TD] [/TD]
[TD]2014.37[/TD]
[TD] [/TD]
[TD]1786.68[/TD]
[TD]2434.34[/TD]
[TD]2175.93[/TD]
[TD]1375.38[/TD]
[/TR]
[TR]
[TD]77571[/TD]
[TD] [/TD]
[TD]50.21[/TD]
[TD] [/TD]
[TD]4799.73[/TD]
[TD]7239.91[/TD]
[TD]6204.31[/TD]
[TD]5383.25[/TD]
[TD]4883.97[/TD]
[TD]5300.31[/TD]
[/TR]
[TR]
[TD]90537[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]126.44[/TD]
[TD]4909.14[/TD]
[TD]4511.42[/TD]
[TD]5032.8[/TD]
[TD]5749.05[/TD]
[TD]2075.37[/TD]
[TD]5066.75[/TD]
[/TR]
</tbody>[/TABLE]


Kind Regards,
Prakash

 

[gaz_chops]

What is the "required data?".

 

[sprakash1704]

What is the "required data?".

Hi,

The required data here is the respective value pertaining to the emp Id for a specific month.

For instance I say Emp 31799 and month Jul = Req data - 1388.69

hope my query is not confusing

Thanks

 

[sprakash1704]

Also Just to add, the input data from which I am going to Pivot will not have all 12 months and there where this query actually raised. However the (sheet Eg A) in which I lookup the data from the pivot is constant, say jun to july will be displayed in the column, so If I pivot with only Jun, jul and aug data, I am unable to extract it in the (Sheet Eg A) with relevent to the emp ID

 

[gaz_chops]

Try Index/Match, have a look at file on dropbox
https://www.dropbox.com/s/itoskegw8m6oivc/sprakash1704.xlsx?dl=0

 

[sprakash1704]

Thanks Gaz, but I am unable to access the link as am in office now , any other options please....

 

[Stridhan]

theres a function called Getpivotdata
You can use it to get data from the summary but perhaps your table dont have the values there.

Instead of using static values in the funtion you can refer to the cells like ive done in my example below.

=GETPIVOTDATA("Sålt",E7,""&R1&"",""&R2&"")

 

[gaz_chops]

This the formula i used, you will need to change the ranges to suit
=INDEX(E18:O35,MATCH(M7,D18:D29,0),MATCH(N7,E15:M15,0))

M7 = Emp ID
N7 = Month (e.g. Aug)

 

[sprakash1704]

Hey Gaz,

would you be able to share a file with example, coz I am unable to decode this formula, I am such a dumb.
I tried to upload a sample file but I dont know how !

See this is sample 1 - Pivot in sheet 2

[TABLE="width: 544"]
<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>[TR]
[TD]Emp ID[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]22705[/TD]
[TD]5263.22[/TD]
[TD]6038.22[/TD]
[TD]4787.53[/TD]
[TD]5081.35[/TD]
[TD]37059.82[/TD]
[/TR]
[TR]
[TD]31799[/TD]
[TD]1182.74[/TD]
[TD]1311.87[/TD]
[TD]712.1[/TD]
[TD]1807.24[/TD]
[TD]13204.25[/TD]
[/TR]
[TR]
[TD]39974[/TD]
[TD]7673.24[/TD]
[TD]7280.15[/TD]
[TD]3753.64[/TD]
[TD]4459.22[/TD]
[TD]37915.34[/TD]
[/TR]
[TR]
[TD]57683[/TD]
[TD]4251.25[/TD]
[TD]5323.53[/TD]
[TD]4084.27[/TD]
[TD]4063.28[/TD]
[TD]30830.79[/TD]
[/TR]
[TR]
[TD]57794[/TD]
[TD]5696.69[/TD]
[TD]6038.89[/TD]
[TD]4378.91[/TD]
[TD]4467.81[/TD]
[TD]31904.02[/TD]
[/TR]
[TR]
[TD]61546[/TD]
[TD]3729.11[/TD]
[TD]5026.33[/TD]
[TD]4609.51[/TD]
[TD]5487.5[/TD]
[TD]28291.54[/TD]
[/TR]
[TR]
[TD]61547[/TD]
[TD]4810.99[/TD]
[TD]3932.63[/TD]
[TD]4715.96[/TD]
[TD]4212.66[/TD]
[TD]30385.31[/TD]
[/TR]
[TR]
[TD]66751[/TD]
[TD]2220.97[/TD]
[TD]5752.75[/TD]
[TD]3794.56[/TD]
[TD]4888.47[/TD]
[TD]32579.15[/TD]
[/TR]
[TR]
[TD]71060[/TD]
[TD]1786.68[/TD]
[TD]2434.34[/TD]
[TD]2175.93[/TD]
[TD]1375.38[/TD]
[TD]15299.12[/TD]
[/TR]
</tbody>[/TABLE]

This is input sheet where I would want to extract the data from Pivot sheet

[TABLE="width: 483"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Emp ID[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[/TR]
[TR]
[TD]22705[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]31799[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]39974[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]57683[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]57794[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]61546[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]61547[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]66751[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]71060[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

So, ideally I dont have data May to Aug, so from Sep I should be able to populate data, does this makes sense ? hope I am not confusing toooooo much! Thanks for your help mate!

 

[gaz_chops]

What are the data ranges of your pivot? Top left cell & bottom right cell?I did upload file to Dropbox!