Annual summary with categories and matching

I am trying to produce a table of annual cost from a monthly data sheet of different countries without the use of pivot table, while replace a column by matching with a reference table.

For example, these are the tables:

Monthly cost:

Country | Item | Date | Cost |
----------------------------------
X | A | Jun-09 | $10 |
__| B | Jul-09 | $5 |
__| D | Jul-09 | $10 |
Y | A | Jun-08 | $20 |
__| C | Jun-09 | $10 |
__| E | Aug-09 | $5 |
----------------------------------

Item reference:

Type | Category | Item |
---------------------------
Food | Meat | A |
_____| Meat | B |
_____| Wheat | C |
Energy| Nuclear | D |
______| Gas | E |
---------------------------

And the resulting table would be:

Year | Country | Category | Cost |
---------------------------------------
2008 | Y | Meat | $20 |
2009 | X | Meat | $15 |
_____| X | Nuclear | $10 |
_____| Y | Wheat | $10 |
_____| Y | Gas | $5 |



Is there anyway to do this through a single step/function? Or do I need to copy the original table to a new worksheet, fill in the blanks, reorder, reformat and filter the columns, and etc?

Originally Posted by Palmetto

Please attach a sample workbook that exactly represent your real structure and clearly shows the expected/desired results and a clear explanation. It will save time and get you a precise answer more quickly.

To attach a file: when posting, scroll down the page until you see the section titled "Attach Files". Click on Manage Attachment. It should be intuitive from there.

Sorry about that. I have attached an example attached, and the purpose is to produce the table in the "Output" tab with annual cost from the "Monthly Cost" tab. The table should include year, country, and lastly category (which you need to match the item column from the "Monthly Cost" tab with the "Item Reference" tab to find the right category.