VLOOKUP with Multiple References

[macrorookie]

Hi,

I'm stuck with my formula. I'm rebuilding an existing formula, but the complexity of the new version has gotten the better of me.

The OLD FORMULA has limitations, such as a single set of assumptions for all years, and different formulas for the first month and the balance of the months. The NEW FORMULA should allow me to have a different set of assumptions for each year (see the "Control" sheet) and a consistent formula for all months (including the first month).

The attached workbook shows a simplified version of my challenge. I also show the old formula - the formula I'm trying to rebuild. If I can solve for the "Sr. Underwriter" row (Row 14), I can adjust that formula for my other needs. Also, as a point of clarification, if I enter any non-zero number in Cell B14, then I get a result for January 2014; however, that's the sort of assumption I'm trying to avoid. I want the formula to refer to the assumptions for the correct year, on the Control sheet (e.g., 2014, 2015, 2016).

A solution would come from someone who has better skills than I have, and I appreciate any help.

[FDibbins]

If I understand you correctly, try this ARRAY formula in C14, copied down and across...

=IF(ISNUMBER(MATCH($A14&C$1,Control!$B$3:$B$26&Control!$G$3:$G$26,0)),1,0)
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[macrorookie]

FDibbins,
Outstanding solutilon! It worked. Thank you very much.

[FDibbins]

Happy to help and thanks for the feedback