Array Formula only works if entered in more than one cell

[Speshul]

I've seen this happen once before (on a shortest formula challenge)

I am working on a solution for http://www.excelforum.com/excel-form...h-vlookup.html

Every solution I have come up with does the same thing, it will work only if array-entered into multiple cells at the same time.

Formulas this holds true with are:
=SUM(INDEX(F3:F23,MATCH(B3:B17,E3:E23,0))*C3:C17)
=IF(NOT(B3:B17="-"),SUM(INDEX(F3:F23,MATCH(B3:B17,E3:E23,0))*C3:C17),"")
=SUM(VLOOKUP(B3:B17,E3:G23,2,0)*C3:C17)



Why is this? The formulas work perfectly, if entered into more than one cell (ANY number of cells). But do not return the right result when only entered into one.

I think VLOOKUP's don't work well in array format but INDEX/MATCH should as far as I know?

[XOR LX]

Hi Speshul.

The results achieved using multiple-cell array-entry are each operating on a different, relative element within the array passed to it. That is how they are designed.

That should not necessarily lead you to believe that the equivalent single-cell array formula will operate over all elements within that array.

At least, not without a little extra coercion. See here for an overview:

http://excelxor.com/2014/08/23/index...ray-of-values/

It resolves mainly around returning an array using INDEX, though if you go through it all you'll see there's a VLOOKUP example as well.

Regards

[Speshul]

It just feels like using the =SUM at the beginning should sum up all contents of the list of values in the array

=INDEX(F3:F23,MATCH(B3:B17,E3:E23,0))*C3:C17 on the sheet in the linked thread returns a 900 which is correct, if you enter into multiple cells it returns {900, something, something...}. With that in mind, I would assume that putting the =SUM in front would result in 5000 in one cell, or an array of {5000;5000;5000} (5000 is just a number, i forget the actual answer to the thread).

My understanding of what is going on is as follows;

If I were to enter {1;2;3;4;5;6} into Column A,
The formula =--(A1:A6=4) returns
{0;0;0;1;0;0}
Multiplied by ROW()
{0;0;0;4;0;0}
Then you would use a SUM to get
{4;4;4;4;4;4}
Which you can array-enter into a single cell to get the result of 4.

However, with the above formulas, entering that same formula in one cell returns a 3.4, but putting it in two or more returns {4;4;4;4;4;4............} That is where my confusion lies. One cell entry returns a number that doesn't exist within the array, nor can it be calculated somehow by the values within the array, but multi-cell entry gives a perfect answer with no other alterations to the formula.

This is more or less as far as my knowledge of array formulas lies, while that understanding can go a long way, I guess I am still missing something. I'll continue looking around. Thanks for the website link by the way, I might just try some of those challenges you post, very tricky looking stuff!