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]

Are the months all date format? Or Text?

 

[sprakash1704]

Just to add below is the data sample

Sheet 1

Name	January	February	March	April
A	12.00	99.00	102.00	114.00
B	17.00	99.00	181.00	263.00
C	22.00	104.00	186.00	268.00
D	27.00	109.00	191.00	273.00
E	32.00	114.00	196.00	278.00
F	44.00	126.00	208.00	290.00
G	56.00	138.00	220.00	302.00
H	68.00	150.00	232.00	314.00
I	69.00	151.00	233.00	315.00
J	70.00	152.00	234.00	316.00
K	78.00	160.00	242.00	324.00
L	86.00	168.00	250.00	332.00
M	94.00	176.00	258.00	340.00

<colgroup><col width="80" span="5" style="width:60pt"> </colgroup><tbody>
</tbody>

Sheet 2

Name	Jan	April
A	12.00	114.00
B	17.00	263.00
C	22.00	268.00
D	27.00	273.00
E	32.00	278.00
F	44.00	290.00
G	56.00	302.00
H	68.00	314.00
I	69.00	315.00
J	70.00	316.00
K	78.00	324.00
L	86.00	332.00
M	94.00	340.00

<colgroup><col width="80" span="3" style="width:60pt"> </colgroup><tbody>
</tbody>

 

[gaz_chops]

We need to add left to extract the 1st 3 char of month then match.
What are the data ranges?

 

[sprakash1704]

No , Gaz, this is the sample data that I have posted.

for instance if in sheet 1 its "Walmart", in sheet 2 it would be "Supercenter"

 

[gaz_chops]

Sorry, i'm confused now, thought it was the month header that was the problem?

 

[sprakash1704]

Sorry Gaz, for the confusion.

I'll rephrase the table.

Sheet 1		Sheet 2
Walmart	=	Supercenter
Amazon	=	Online store
Target	=	Superstore
Bestbuy	=	Consumer Electronics

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

Sheet1

Name	Walmart	Amazon	Target	Bestbuy
A	12.00	99.00	102.00	114.00
B	17.00	99.00	181.00	263.00
C	22.00	104.00	186.00	268.00
D	27.00	109.00	191.00	273.00
E	32.00	114.00	196.00	278.00
F	44.00	126.00	208.00	290.00
G	56.00	138.00	220.00	302.00
H	68.00	150.00	232.00	314.00
I	69.00	151.00	233.00	315.00
J	70.00	152.00	234.00	316.00
K	78.00	160.00	242.00	324.00
L	86.00	168.00	250.00	332.00
M	94.00	176.00	258.00	340.00

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

Sheet2

Name	Supercenter	Consumer Electronics
A	12.00	114.00
B	17.00	263.00
C	22.00	268.00
D	27.00	273.00
E	32.00	278.00
F	44.00	290.00
G	56.00	302.00
H	68.00	314.00
I	69.00	315.00
J	70.00	316.00
K	78.00	324.00
L	86.00	332.00
M	94.00	340.00

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

 

[yshridhar]

sheet2 B2 =INDEX(Sheet1!$A$1:$E$14,MATCH($A2,Sheet1!$A$1:$A$14,0),MATCH(VLOOKUP(B$1,$H$1:$I$5,2,0),Sheet1!$A$1:$E$1,0))

H1:I5 changes in the names Sheet2 (H-New name) Sheet1 (L-Old name)

Sridhar

 

[gaz_chops]

Yep thats what I would do, set up a small lookup table.

 

[sprakash1704]

Sure, let me try this and will get back to you folks!!!

 

[sprakash1704]

This works perfectly fine guys! Thank you Shridhar and Gaz!!!