Index? Sumproduct?

Isn't there something wrong with your example? The code is the same for all 3 rates. So how is Excel meant to know which one to pick? Can you take a look again at this?

If the values for the codes are made unique, then it all works fine with INDEX:MATCH (attached).

A customer may have 20 different rates for different date ranges but their code will always be the same. (that's why I set my example workbook with the same customer codes- that's the part I cant get around)

Example
Customer A has 3 shipments, 1 on jun 25, 1 on jul 2 and 1 on jul 9.
His rate from jun 23 to jun 29 is 5$
his rate from jun 30 to jul 6 is 10$
his rate from jul 7 to jul 13 is 15$

Dohh!! The penny drops. Try this (ARRAY FORMULA< set with CTRL + SHIFT + ENTER)

Formula:

Please Login or Register  to view this content.

Dohh again.. I forgot to build in the customer code...

Formula:

Please Login or Register  to view this content.

A Non-Array version of the formula suggested by Glenn would be like this with the specified range...

Please Login or Register  to view this content.

You're welcome. Thanks for the feedback.
If that takes care of your question, please mark your thread as solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.

Thanks for the feedback....

I always forget the non-array version for multiple criteria...

Originally Posted by Glenn Kennedy

I always forget the non-array version for multiple criteria...

People seem to think that adding an additional INDEX function to avoid an array formula is better.

It's not!

Tony, Interesting comment. Why do you say that (for my benefit as much as that of the OP)?

I say it because its true!

I tested these 2 formulas:

Array entered:

=INDEX(C:C,MATCH(1,(A1:A1000=10)*(B1:B1000=10),0))

Non-array entered:

=INDEX(C:C,MATCH(1,INDEX((A1:A1000=10)*(B1:B1000=10),0),0))

The lookup values were found on row 500.

Here are the calculation times for these 2 formulas:

Data Range

	D	E	F	G	H	I	J
1		Test1	Test2	Test3	Test4	Test5	Average
2	Array	0.00093	0.00090	0.00090	0.00090	0.00090	0.000906
3	Non-array	0.00094	0.00092	0.00092	0.00093	0.00093	0.000928

The results you get will depend on the configuration of your machine.

So, what advantage was gained by using the non-array formula?

Maybe not having to remember to CSE is a benefit but the non-array version has no performance advantage over the array version.

You can do your own tests. The timer code can be found here:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

Just remember that the results you get depend on what version(s) of Excel you test in and how your machine is configured.

Yes.

I once did a similar exercise in which I replaced all necessary parts (about five) of a longish array formula with INDEX constructions in order to force the equivalent array-processing.

To cut a long story short, the formula practically crashed on me.

I think that substituting a single INDEX has some merit, and I often use this method myself, though I would hesitate to suggest that it actually constitutes an "improvement" to do so.

I would even go as far as to say that using INDEX as a pure substitute for CSE (and certainly when substituting more than one, as I attempted) is not only not as "good" an achievement (required key-combination aside, of course!) as some think, but by all accounts leaves you, at least in terms of efficiency and calculation speed, actually noticeably worse.

It is an extra function call, after all...

Regards

Originally Posted by Tony Valko

The lookup values were found on row 500.

What if lookup value is found on row 5000.
I tested both the Array and Non-Array formulas five times and the minimum time for the Array formula was 0.00404 and for the Non-Array it was 0.00402.

So that means the difference between the two is 0.00002 seconds.

That's a negligible difference and so is the difference shown in my post.

That means Non-Array formula is doing better than Array formula for the larger data set.

Moreover when referencing the whole column, Array takes 0.33905 and Non-Array takes 0.33559.

I just tested the formulas on 50,000 rows...

Lookup values found on row 45,000 although it doesn't matter what row the lookup values are found on. Both formulas evaluate ALL cells referenced.

Data Range

	E	F	G	H	I	J	K
1		test1	test2	test3	test4	test5	Average
2	Array	0.03470	0.03541	0.02909	0.03020	0.02735	0.031350
3	Non-array	0.03337	0.02752	0.03677	0.03331	0.03650	0.033494

I would consider the difference to be negligible.

Originally Posted by Tony Valko

I would consider the difference to be negligible.

Correct.

Originally Posted by sktneer

Moreover when referencing the whole column, Array takes 0.33905 and Non-Array takes 0.33559.

I would consider the difference to be negligible.