How to populate a table based on the cell location of given datas?

Hi,

I'm a new intern and my boss has given me an excel table to fill in. I know how to do it manually but given the amount of data, using excel functions would be a more appropriate and accurate method. I tried researching how to use the functions but the information overflow isn't helping. Can anyone help me solve this issue or at least guide me towards what exact functions I should be learning to use?

I have attached here a sample of what I need to do, so please take a look at the excel file.
I am using Excel 2010.

So what I have is pretty much a table with information regarding sales by people categorized my Quarter 1 (Q1), Q2 and Q3. Each sales then is classified as Type 1, 2 and 3. All I need done is to place the sales on its equivalent place on the next table.

For example, please look at the yellow number. Smith's Q1 sales of 45987 is a Type 2. Now that number is located on cell C4, how can I use a function to have that same number placed on its equivalent place on the next table in cell I4?

Is easy to simply copy and paste, but each spreadsheet has over 700 entries...Besides my boss would like to in the future be able to update a few entries, so if using functions I believe that if a number is updated, so will it be at all equivalent cells right?

I am really feeling lost... And would appreciate any help!

Thanks alot

Hi benishiyo,

Thank you for your response. I have copied and paste the =SUMPRODUCT(($C$3:$E$3=LEFT(H$3,2))*($F4=RIGHT(H$3,2))*($C4:$E4))

into cell H4 and nothing happened. And yes the rows should be "locked" since Smith for example would have more than on sales entry.
Pardon my ignorance, I'm just trying to understand the formula, what cell is (H$3,2) related to?

Thanks for the help! I finally got the sample given to work! Thanks so much! But now that I tried applying the formula to a bit more extensive spreadsheet, it isn't working and I seem to not figure out how to fix the formula. Could you possibly help me?

I have attached here the spreedsheet I am working with and changed names and numbers around, just for the sake of example. The concept is the same, the difference is that now besides T1, T2, and T3, there is M which belongs to the columns named Mongolia... i'm totally lost!

Here is the formula I have adapted to but not finished because I am stuck...

=SUMPRODUCT(($C$1:$F$1=LEFT(M$1,2))*($L2=RIGHT(M$1,2))*($C$2:$F$2))

I had placed this formula on cell M2 and when across and down only provides me with zeros, obviously is wrong.
I am guessing that the following are wrong...

The number 2 in: LEFT(M$1,2) and RIGHT(M$1,2)

Now I only manually populated the spreadsheet up to row 5, should be enought for testing I reckon.

Let me know if I can better explain the table to you.

Is now working perfectly! Thank you so much!!!!!!!!!!!!