yogi_Return Cell Contents From An Array Based On Three Cell-Referenced Criteria

                                          Google Spreadsheet   Post  #1190
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 11, 2013
user JC_16 (http://productforums.google.com/forum/?zx=ppk7ihil9lij#!category-topic/docs/spreadsheets/8Rb6dMx8XVo)
In Excel I would use a Sum(if) array formula (with nested if statements) to solve this...

arraytest spreadsheet

I need to find a formula that will search an array for a cell based on the criteria established in three other cells.  There are "categories" in row 1 (merged cells), "subcategories" in row 2, and "divisions" in column A.  At my old office they would call this a "three dimensional matrix" (& we'd avoid using merged cells, which could complicate matters in Excel 2003, and instead use "center across selection" formatting to mimic merged cells - I am hoping Google spreadsheet does not have a problem running formulae referencing merged cells).

I need a formula that will return the cell contents at the intersection of the requested "category, subcategory, and division."

-----------

The ultimate goal for the actual spreadsheet I am designing is to have two mirrored sheets, which share "category, subcategory, and division" headings.  Table B will have the actual data (minus columns of ancillary descriptive data, and not every cell in the array will be populated), while Table A will be driven by a dropdown that allows the user to switch between viewing the data as it appears on Table B, or simply viewing tick marks if the respective cell is populated.  So the formula I am asking for help designing will be part of the driver for the Table A array.  The Table A array formula will be a little more complicated than that - besides adding an If() to the formula to reference the dropdown, I will need to add a condition that will encapsulate the tick mark in parenthesis if the original data is also parenthetical - but I can't even get past step 1, which is figuring out the formula to use to locate data in an array from three reference points.

I have been watching YouTube videos and reading up on Index(), Arrayformula(), Filter(), etc., but there is a serious dearth of documentation/tutorials/examples/etc for almost all things Google spreadsheet, which I find quite disheartening, especially coming from Excel, where the information cloud is immeasurable.  I appreciate the difference in age, scope, application, market share, etc., but I am still disappointed there isn't at least a link to examples and tutorials in every cell in the "Learn More" column on the Google spreadsheets function list.

--------------------------------------------------------------------------------------------------------------------

let us have a look at the following