Trouble with Array Text Output and Criteria

[andy.b53]

I've been having trouble figuring this out. I am using arrays to evaluate a specific combination of criteria (color code, color, Toy) and return a maximum amount (see attached file - Example 1). I can get that to work, however, if one of the criteria is entirely missing within that combination, I'd like it to return with text that says, "Color Code Missing" (or whatever it is that is missing - see Example 2).

If there is only one combination that does match the criteria, I'd like it to return that total cost (see Example 3)

Whenever I manually add missing criteria to one of the Inventories to make it complete, the Formula/Array will often times say the criteria is still missing.

Thoughts?

-----

Attachment not working, see google drive link for the file: https://drive.google.com/open?id=0B9...jVHdW5yLW9ZUm8

[Glenn Kennedy]

Here's a partial solution (formulae set for example 2):

Max cost (D18 copied down):

=MAX(IF($H$18:$H$26=A18,IF($I$18:$I$26=B18,IF($J$18:$J$26=C18,$K$18:$K$26))))

Error message (E18, copied down):

=IF(ISNUMBER(MATCH(A18&B18&C18,$H$18:$H$26&$I$18:$I$26&$J$18:$J$26,0)),"-","Something missing")

Both are array formulae, which are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...