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]

You're welcome.

 

[sprakash1704]

Just out of curiosity, is there any other option to do this without the temp table for vlookup ?

 

[gaz_chops]

It is generally much easier to manage any changes/additions to the table.
How many are in the lookup table, are they likely to increase/change?

 

[sprakash1704]

Sorry Gaz, missed your msg , as of now its just 10 rows !

 

[sprakash1704]

Hey Gaz, just a quick question on the match formula, how do I use trim in match, coz I tried and its not working

 

[sprakash1704]

np, I used Ctrl + Shift + Enter and used trim and its working

 

[gaz_chops]

I really would stick with the table, but alternative is like
=INDEX(Sheet1!$A$1:$E$14,MATCH($A2,Sheet1!$A$1:$A$14,0),MATCH(LOOKUP(B$1,{"Consumer Electronics","Online Store","Supercenter","Superstore",""},{"Bestbuy","Amazon","Walmart","Target"}),Sheet1!$A$1:$E$1,0))