On Sun, 3 Jul 2005 04:55:03 -0700, "BartDesc"
wrote:

It seemed simple:
={SUM(VLOOKUP((F15:F19);DataRLambda;2;FALSE))}
should work, but doesn't. F15 is used every time for evaluation in VLOOKUP.
I have some experience in excel so I don't think it's any obvious mistake.
My conclusion is that using Vlookup in a formula array doesn't work.
Can anybody second that?
Did anybody find an elagant workaround? (I could create extra calculations
in extra columns or even some VBA programming).

Thanks,
Bart

VLOOKUP does not seem to work in an array formula, but rather to return only a
single result per line of code.

I did the following in XL2002:

It is interesting, however, that if you enter a very similar array formula in
more than one adjacent cell (vertical or horizontal), then the SUM function
works as expected in both (or all) cells.

In other words, let us assume your formula above is in A1.

Select A1 & A2 simultaneously.
Enter the formula into the formula bar.
Hit <ctrl<shift<enter and the formula will fill both cells; and the SUM
function SUMS all of the entries.

Also, if you enter this as an array formula as above, but without the SUM and
in cells A1:A5 as above, it will return each individual lookup in the
appropriate cell. e.g. The lookup for F15--A1; F16--A2; etc.

Obviously this is undocumented behavior and I'm not sure what, exactly, is
going on; or whether this behavior will persist in future versions.

Perhaps someone more knowledgeable than I can answer.

An "elegant" solution might be to enter the formula into 2 adjacent cells, then
hide one of them. Perhaps with a custom format which serves as a label: e.g.
Format/Cells/Number/Custom Type: "Label"






--ron