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]

Are the months all date format? Or Text?

 

[sprakash1704]

Just to add below is the data sample

Sheet 1

[TABLE="width: 400"]
<colgroup><col width="80" span="5" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl150, width: 80"]Name[/TD]
[TD="class: xl151, width: 80"]January[/TD]
[TD="class: xl151, width: 80"]February[/TD]
[TD="class: xl151, width: 80"]March[/TD]
[TD="class: xl151, width: 80"]April[/TD]
[/TR]
[TR]
[TD="class: xl152"]A[/TD]
[TD="class: xl153"] 12.00 [/TD]
[TD="class: xl153"] 99.00 [/TD]
[TD="class: xl153"] 102.00 [/TD]
[TD="class: xl153"] 114.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]B[/TD]
[TD="class: xl153"] 17.00 [/TD]
[TD="class: xl153"] 99.00 [/TD]
[TD="class: xl153"] 181.00 [/TD]
[TD="class: xl153"] 263.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]C[/TD]
[TD="class: xl153"] 22.00 [/TD]
[TD="class: xl153"] 104.00 [/TD]
[TD="class: xl153"] 186.00 [/TD]
[TD="class: xl153"] 268.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]D[/TD]
[TD="class: xl153"] 27.00 [/TD]
[TD="class: xl153"] 109.00 [/TD]
[TD="class: xl153"] 191.00 [/TD]
[TD="class: xl153"] 273.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]E[/TD]
[TD="class: xl153"] 32.00 [/TD]
[TD="class: xl153"] 114.00 [/TD]
[TD="class: xl153"] 196.00 [/TD]
[TD="class: xl153"] 278.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]F[/TD]
[TD="class: xl153"] 44.00 [/TD]
[TD="class: xl153"] 126.00 [/TD]
[TD="class: xl153"] 208.00 [/TD]
[TD="class: xl153"] 290.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]G[/TD]
[TD="class: xl153"] 56.00 [/TD]
[TD="class: xl153"] 138.00 [/TD]
[TD="class: xl153"] 220.00 [/TD]
[TD="class: xl153"] 302.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]H[/TD]
[TD="class: xl153"] 68.00 [/TD]
[TD="class: xl153"] 150.00 [/TD]
[TD="class: xl153"] 232.00 [/TD]
[TD="class: xl153"] 314.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]I[/TD]
[TD="class: xl153"] 69.00 [/TD]
[TD="class: xl153"] 151.00 [/TD]
[TD="class: xl153"] 233.00 [/TD]
[TD="class: xl153"] 315.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]J[/TD]
[TD="class: xl153"] 70.00 [/TD]
[TD="class: xl153"] 152.00 [/TD]
[TD="class: xl153"] 234.00 [/TD]
[TD="class: xl153"] 316.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]K[/TD]
[TD="class: xl153"] 78.00 [/TD]
[TD="class: xl153"] 160.00 [/TD]
[TD="class: xl153"] 242.00 [/TD]
[TD="class: xl153"] 324.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]L[/TD]
[TD="class: xl153"] 86.00 [/TD]
[TD="class: xl153"] 168.00 [/TD]
[TD="class: xl153"] 250.00 [/TD]
[TD="class: xl153"] 332.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]M[/TD]
[TD="class: xl153"] 94.00 [/TD]
[TD="class: xl153"] 176.00 [/TD]
[TD="class: xl153"] 258.00 [/TD]
[TD="class: xl153"] 340.00 [/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2

[TABLE="width: 240"]
<colgroup><col width="80" span="3" style="width:60pt"> </colgroup><tbody>[TR]
[TD="class: xl150, width: 80"]Name[/TD]
[TD="class: xl151, width: 80"]Jan[/TD]
[TD="class: xl151, width: 80"]April[/TD]
[/TR]
[TR]
[TD="class: xl152"]A[/TD]
[TD="class: xl153"] 12.00 [/TD]
[TD="class: xl153"] 114.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]B[/TD]
[TD="class: xl153"] 17.00 [/TD]
[TD="class: xl153"] 263.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]C[/TD]
[TD="class: xl153"] 22.00 [/TD]
[TD="class: xl153"] 268.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]D[/TD]
[TD="class: xl153"] 27.00 [/TD]
[TD="class: xl153"] 273.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]E[/TD]
[TD="class: xl153"] 32.00 [/TD]
[TD="class: xl153"] 278.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]F[/TD]
[TD="class: xl153"] 44.00 [/TD]
[TD="class: xl153"] 290.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]G[/TD]
[TD="class: xl153"] 56.00 [/TD]
[TD="class: xl153"] 302.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]H[/TD]
[TD="class: xl153"] 68.00 [/TD]
[TD="class: xl153"] 314.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]I[/TD]
[TD="class: xl153"] 69.00 [/TD]
[TD="class: xl153"] 315.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]J[/TD]
[TD="class: xl153"] 70.00 [/TD]
[TD="class: xl153"] 316.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]K[/TD]
[TD="class: xl153"] 78.00 [/TD]
[TD="class: xl153"] 324.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]L[/TD]
[TD="class: xl153"] 86.00 [/TD]
[TD="class: xl153"] 332.00 [/TD]
[/TR]
[TR]
[TD="class: xl152"]M[/TD]
[TD="class: xl153"] 94.00 [/TD]
[TD="class: xl153"] 340.00 [/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="width: 291"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sheet 1[/TD]
[TD][/TD]
[TD]Sheet 2[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]=[/TD]
[TD]Supercenter[/TD]
[/TR]
[TR]
[TD]Amazon[/TD]
[TD]=[/TD]
[TD]Online store[/TD]
[/TR]
[TR]
[TD]Target[/TD]
[TD]=[/TD]
[TD]Superstore[/TD]
[/TR]
[TR]
[TD]Bestbuy[/TD]
[TD]=[/TD]
[TD]Consumer Electronics[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1

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

Sheet2

[TABLE="width: 274"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Supercenter[/TD]
[TD]Consumer Electronics[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD] 12.00[/TD]
[TD] 114.00[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD] 17.00[/TD]
[TD] 263.00[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD] 22.00[/TD]
[TD] 268.00[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD] 27.00[/TD]
[TD] 273.00[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD] 32.00[/TD]
[TD] 278.00[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD] 44.00[/TD]
[TD] 290.00[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD] 56.00[/TD]
[TD] 302.00[/TD]
[/TR]
[TR]
[TD]H[/TD]
[TD] 68.00[/TD]
[TD] 314.00[/TD]
[/TR]
[TR]
[TD]I[/TD]
[TD] 69.00[/TD]
[TD] 315.00[/TD]
[/TR]
[TR]
[TD]J[/TD]
[TD] 70.00[/TD]
[TD] 316.00[/TD]
[/TR]
[TR]
[TD]K[/TD]
[TD] 78.00[/TD]
[TD] 324.00[/TD]
[/TR]
[TR]
[TD]L[/TD]
[TD] 86.00[/TD]
[TD] 332.00[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD] 94.00[/TD]
[TD] 340.00[/TD]
[/TR]
</tbody>[/TABLE]

 

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