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!!!!!

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

<colgroup><col><col span="9"></colgroup><tbody>
</tbody>

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

Emp ID	Sep	Oct	Nov	Dec	Grand Total
22705	5263.22	6038.22	4787.53	5081.35	37059.82
31799	1182.74	1311.87	712.1	1807.24	13204.25
39974	7673.24	7280.15	3753.64	4459.22	37915.34
57683	4251.25	5323.53	4084.27	4063.28	30830.79
57794	5696.69	6038.89	4378.91	4467.81	31904.02
61546	3729.11	5026.33	4609.51	5487.5	28291.54
61547	4810.99	3932.63	4715.96	4212.66	30385.31
66751	2220.97	5752.75	3794.56	4888.47	32579.15
71060	1786.68	2434.34	2175.93	1375.38	15299.12

<colgroup><col><col span="2"><col><col span="2"></colgroup><tbody>
</tbody>

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

Emp ID	May	Jun	Jul	Aug	Sep	Oct
22705
31799
39974
57683
57794
61546
61547
66751
71060
Grand Total

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

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!