Using Vlookup in formula arrays

B

BartDesc

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
 
K

KL

Hi,

As far as I know VLOOKUP accepts arrays in its 3rd argument, but not in the
1st one.
As an alternative you can try something like this:

=SUMPRODUCT((A1:A10=TRANSPOSE(F15:F19))*B1:B10)

This assumes that your named range DataRLambda is located in [A1:B10]

Regards,
KL
 
R

Ron Rosenfeld

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
Click to expand...

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
 
A

Aladin Akyurek

I have the impression that you want something like:

=SUMPRODUCT(SUMIF(INDEX(DataRLambda,0,1),$F$15:$F$19,INDEX(DataRLambda,0,2)))

You can eliminate INDEX() for more speed by substituting the appropriate
ranges instead of using DataRLambda...

Lets say that DataRLambda refers to A2:D20. The SumProduct formula would
become:

=SUMPRODUCT(SUMIF($A$2:$A$20,$F$15:$F$19,$B$2:$B$20))

Replace comma's with semi-colons for your version of Excel.
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
Click to expand...

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.
 
R

Ron Rosenfeld

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
Click to expand...


Doing a bit more research reveals that the LOOKUP worksheet function seems to
work, in array formulas, as you desire. However, the lookup vector needs to be
in ascending order or you may get the wrong result. Given your FALSE argument
in the VLOOKUP function you posted, this may not be an option for you.


--ron
 
Top