More than Multiple Lookups: Conditional Multiple Lookups

you could try this

Curious... it's not really clear how the INDEX/MATCH approach will differ from your embedded IF & LOOKUP approach when you have different numbers of criteria to concatenate ?

The use of the embedded INDEX to negate need for Array will prove no more efficient than the LOOKUP(2,1/(....)) approach - neither in truth are great performers...
some would argue that when used against larger data sets an Array will perform no worse and in some instances will perform better... to illustrate in terms of INDEX usage ...

=SUMPRODUCT(--(A1:A1000="a"),--(B1:B1000="b"),C1:C1000)

and

=SUM(IF((A1:A1000="a")*(B1:B1000="b"),C1:C1000))
CSE

are still used in preference to INDEX even though we can (errors permitting) perform the same with:

=SUM(INDEX((A1:A1000="a")*(B1:B1000="b")*C1:C1000,0))

why ? Well because INDEX is quite slow... amplified with big datasets.

it also looks good but

Please Login or Register  to view this content.

would also
give same return for

Please Login or Register  to view this content.

depends on what data you are using
having said that i was thinking of going down the sumproduct route

I confess I did not actually look at your file at any point and the point I was making about SUMPRODUCT was really just to illustrate the fact that using INDEX to circumvent need for Arrays is only practical so far
(ie we use SUMPRODUCT and Arrays in preference to using an embedded INDEX for a reason).

All that being said, having now looked at your file... yes it would appear that SUMPRODUCT would satisfy requirements in form of either:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

In terms of how SUMPRODUCT works, coercion (and use of double unary (--)) I would point you to the SUMPRODUCT link in my sig. - Bob Phillips' white paper on the topic.

In terms of alternatives to the above - the same principles could be applied to LOOKUP

Please Login or Register  to view this content.

the above, unlike SUMPRODUCT, will work if the result_vector is populated with text as opposed to numerics but unlike SUMPRODUCT would not aggregate numerical results should multiple records exist for any given combination
(ie think of SUMPRODUCT as an aggregation function whereas LOOKUP is singular)

Generally speaking however...

The downside to both SUMPRODUCT & LOOKUP(2,1/(...)) based approaches is that they are not particularly efficient... in these cases for users pre XL2007 (ie SUMIFS not viable) my advice is always to add a concatenation INDEX at source, eg:

Please Login or Register  to view this content.

You can then, courtesy of that simple concatenation, dispense with the need for SUMPRODUCT and/or LOOKUP altogether, eg

to replace SUMPRODUCT with far more efficient SUMIF

Please Login or Register  to view this content.

to replace LOOKUP(2,1/(...)) with more efficient INDEX/MATCH

Please Login or Register  to view this content.

and again to iterate both approaches are valid depending on the nature of your return range be it numeric / non-numeric and if numeric depending on whether multiple records require aggregation or not.