Lookup/Index-Match-Match using segments

Is it possible to return a value or a sum of values using segmented lookup criteria. The Sample 1 spreadsheet (attached) explains it better.

Grateful for any ideas.

Thanks

You could use a SUMPRODUCT such that:

Please Login or Register  to view this content.

This isn't a great approach, elegant but not particularly efficient.

I would personally advise you create a concatenation of B:D in A such that:

Please Login or Register  to view this content.

Then you can dispense with SUMPRODUCT and use a standard SUMIF which is far more efficient in the long run

Please Login or Register  to view this content.

It is used to evaluate the result of the SEARCH.

A SEARCH function will return a number if the criteria string is found - if not it will return an Error.

The ISNUMBER is used to manipulate the output of the SEARCH to a Boolean of True/False. If found and Search returned a Number the ISNUMBER returns TRUE, if not found and Search returned an Error value then ISNUMBER returns FALSE.

The Boolean output of the ISNUMBER is then coerced via double unary (--) to it's integer equivalent of 1 for TRUE and 0 for FALSE. These integers are subsequently used when calculating the Product.

You could try this

I assumed blank cell means any character?

Please Login or Register  to view this content.

copied down!

Sorry my bad - was on double unary auto pilot... doh.

In this instance we used * rather than -- as our coercer, put simply this is because in this instance our ranges are not of equal dimension, ie:

6x1
B4:B9, C4:C9, D4:D9

6x6
E4:J9

1x6
E3:J3

so a double unary approach is not viable here... when it is viable it is normally used as it's regarded as slightly more efficient... there are pros and cons to both methods (-- and *) ... the link in the Sig offers more info on the subject.

Just as a reference with this sheet all the formulas are fairly close in speed!

I also tried a mod of the sum if formula

RIGHT("*"&B15&":",2)&RIGHT("*"&C15&":",2)&RIGHT("*"&D15,1)

Rather than

IF($B15="","*",$B15)&":"&IF($C15="","*",$C15)&":"&IF($D15="","*",$D15)

SUMIF will be considerably quicker than Sumproduct/Array when used with very large ranges and/or in very large quantities (amplified if used in Volatile fashion) ... if the datasets are insignificant in size then there won't be a massive difference I agree.