Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Vlookup in formula arrays
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 |
#2
|
|||
|
|||
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 "BartDesc" wrote in message ... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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$2 0)) Replace comma's with semi-colons for your version of Excel. 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 -- [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. |
#5
|
|||
|
|||
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Vlookup formula using tax yables | Excel Worksheet Functions | |||
Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
vlookup with table_array constructed from mid formula | Excel Worksheet Functions |