Nesting VLOOKUP in IF/vice versa & Pivot Table

I've been wrestling with this (& ransacking these boards) for way too long now ... I'm "taking the first step" & admitting I need help.

I've attached a sample/equivalent workbook of what I'm working on which will hopefully make it clear(er).
>There are two worksheets/month. Both worksheets (represent 2 different categories) are structured the same, two columns: model code & $ amount. >The model codes change (in # and actual model), between categories and month.
>The data for each month rolls up into a year-to-date summary worksheet, with 4 columns: Model (includes all models YTD, each only listed once), category1 YTD, category 2 YTD, & Total YTD).

Previously this had been done by manually entering any new models for the month into the rows in the YTD summary sheet. And the totals for each model (highlighted in yellow in the YTD tab in my sample) were just done by an adding formula, with the new month's data manually entered into each individual cell at the end of the formula (...+X). I know there's a much better way to do/automate this! (there are a lot more models than I've put in my sample aka it's way too time consuming manually).

My problem is twofold:
1. (main issue) I have been trying to do this using various IF statements nested in VLOOKUPS, and vice versa, but the issue that arises is for models in the summary sheet that don't exist in a given (month's) table. I want the value for those models (for that specific month) to be zero, but I cannot figure out how to get that to work in my formula. The only piece that works for me thus far is =VLOOKUP(A3, 'Jan Cat1'!A2:B18, 2, FALSE), but I've tried nesting it in IF statements, nesting IF statements in it, using ANDs & ORs, no avail.
I'm not even sure any of these options are the best ways to reach what I'm ultimately trying to do. A pivot table may be better? But I will need to keep/preserve the summary sheet for each month (so there cannot just be one big updated master pivot table).

2. If I could find a way to automate/refresh & update the row of models each month, it would be the sprinkles on the icing of this cupcake.

I am at a total loss, but I know this can be done somehow & I'm probably just missing it. Thanks!

Mubashir's solution will work but it will not be correct if there is more than a single occurence of each code on a worksheet. I have a solution that is shorter, calculates more quickly and requires less maintenance. First, you set up your workbook so that you already have the month/category sheets in place. My solution requires a number of named ranges and I have set up the ranges and worksheets as far as April - to demonstrate the solution. The formula used is:

Please Login or Register  to view this content.

See the attached for the implementation.

WOW. This is amazing--thank all three of you very much!! I do need to have a monthly YTD summary sheet for each month in the workbook. For example (just to be totally clear):
Workbook by January has:
Jan-Cat1, Jan-Cat2, Jan-YTD Summary: Cat1, Cat2 & Total (just Jan data)

Workbook by February has:
Jan-Cat1, Jan-Cat2, Jan-YTD Summary (from above), Feb-Cat1, Feb-Cat2, Feb-YTD Summary: Cat1, Cat2 & Total (sums of Jan & Feb data)

Workbook by March has:
Jan-Cat1, Jan-Cat2, Jan-YTD Summary (from above), Feb-Cat1, Feb-Cat2, Feb-YTD Summary: Cat1, Cat2 & Total (sums of Jan & Feb data), Mar-Cat1, Mar-Cat2, Mar-YTD Summary: Cat1, Cat2 & Total (sums of Jan, Feb & Mar data)
(and so on through the year...)

Thus far I think Justin's solution best meets my needs for the monthly structure and updating... but after reading the volatility link you provided, Donkey, that does make me nervous. So I have some additional questions(thank you all, again, SO much):
1. Is it correct that the volatility risk only applies if changes are made to the sheets/cells that are linked or related to the formulas? ie, if no one changes the prior month's data, is there still some type of volatility risk? If not, (because I trust myself with the workbook & editing it), if I were to just lock/password protect the sheets & workbook, would that remove the volatility risk? (aside from my own human error )

2.If there is still volatility risk, or even regardless, would it be possible/feasible to accomplish what I'm trying to do with pivot tables? I'm much less familiar with them, but what I'm wondering is if I could create an additional master (raw) data tab with 3 columns (Model, Cat1, Cat2), dump the data into it each month, & then create a pivot table to sum by model (rows) for Cat1, Cat2 & Total, which I could then refresh each month (a plus being any new models would automatically be entered/updated in the table by refreshing)... BUT my caveat here is how would I preserve the summary data for prior months. Can pivot tables/tabs easily be copied & pasted, or changed to include additional months? & would this even be a reasonable option compared to what I've already have here?

3. Just so my understanding is clear (because I feel like the more I learn about excel, the more clueless I also become), none of these solutions update the models in the master summary, right? that would still need to be done by hand? (if so, it's no big deal for me)

Thanks again!

To clarify/possibly figure out the very last piece of the puzzle, I've attached an updated sample spreadsheet w/ what I've got thus far. My last remaining question is what can I do so that the (new) models would automatically update monthly? Could create/convert this into a pivot table? Or if there's any other way to do that? Or is it not worth the effort?

Thanks!!

I have cheated! For my own selfish reasons, I have re-formatted your approach to the problem. I realise that you are probably seeking a solution to an already established model, but its often the case that the spreadsheet design does not lend itself readily to simple and direct solutions.

If its possible I think you would benefit from a re-design. Take a look at the attached. Depending upon your level of familiarity with DSUM, Query Tables, SQL and Data Tables....you may wish to carefully follow the instructions.

This design would allow you to easily generate YTD reports and update model numbers in those reports without needing macros or tricky formulae. I intend to use my solution as they basis for an article so, if its possible, I would love to know some of the background as to what you are doing (what is the context?) and why your spreadsheet is set up as it is.

There is a solution that does not require a single formula or macro and that does not require you to rearrange data!!! All you need to do is give a range name to each of the data sets - i.e. JanCat1, JanCat2 and so on.... and then when you wish to create a month summary sheet use Data | Consolidation.

The nice thing about Data | Consolidation is that it extracts the model numbers from the ranges you wish to consolidate - and pastes only one instance of each number in the consolidation range. Effectively, it updates the model numbers 'automatically' as you require.

The steps to create a summary sheet would then be as follows:

1. Name the JanCat1,JanCat2,FebCat1,FebCat...etc ranges (include the headings but not the totals)
2. For a February summary (a) Select a blank cell in the middle of nowhere (b) choose Data | Consolidate...
3. In the Reference: box press F3 to paste the JanCat1 name into it.
4. Choose Add to add the name to the All references: box
5. Repeat above step for JanCat2, FebCat1, FebCat2
6. Select the Use labels in Top row, Left column check boxes
7. Choose OK.

See the attached to get a feel for how to do this. Of course, the major drawback is that if you later change the data - you have to repeat Data | Consolidation to refresh the summary report. Fortunately, Excel remembers the consolidation ranges.