| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
hlookup in array
which elements, if any, of an hlookup formula, can be used dynamically in an
array? In other words, if I enter an hlookup formula as array (within a sum function), where can I have a ":" that let's a part of the formula shift? for example, if I have {=SUM(HLOOKUP(G14-D14:G14+1,$D$26:G27,2,FALSE))}, will it take the D14:G14, and give me the sum of the formula with every iteration of D14, E14, F14, and G14 in that spot? I cannot seem to get it to do the above. Am I doing something wrong? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
hlookup in array
All kind of confusing there. May I suggest using SUMPRODUCT()
Not sure what you are trying to sum... =SUMPRODUCT(--(D14:G14=whatever you're comparing),D15:G26) perhaps? "BorisS" wrote: which elements, if any, of an hlookup formula, can be used dynamically in an array? In other words, if I enter an hlookup formula as array (within a sum function), where can I have a ":" that let's a part of the formula shift? for example, if I have {=SUM(HLOOKUP(G14-D14:G14+1,$D$26:G27,2,FALSE))}, will it take the D14:G14, and give me the sum of the formula with every iteration of D14, E14, F14, and G14 in that spot? I cannot seem to get it to do the above. Am I doing something wrong? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
hlookup in array
can I ask you to quickly have a look at my post for 'up and down array'? I
thought of Sumproduct, but maybe wasn't thinking straight. The noted post has maybe a better explanation (I did realize this one was a bit convoluted). Would love to solve this, so if you can help me get it straight, very much appreciated. -- Boris "Sean Timmons" wrote: All kind of confusing there. May I suggest using SUMPRODUCT() Not sure what you are trying to sum... =SUMPRODUCT(--(D14:G14=whatever you're comparing),D15:G26) perhaps? "BorisS" wrote: which elements, if any, of an hlookup formula, can be used dynamically in an array? In other words, if I enter an hlookup formula as array (within a sum function), where can I have a ":" that let's a part of the formula shift? for example, if I have {=SUM(HLOOKUP(G14-D14:G14+1,$D$26:G27,2,FALSE))}, will it take the D14:G14, and give me the sum of the formula with every iteration of D14, E14, F14, and G14 in that spot? I cannot seem to get it to do the above. Am I doing something wrong? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HLOOKUP: specify table array in a cell | New Users to Excel | |||
| array formula with if and hlookup | Excel Worksheet Functions | |||
| Vlookup / Match / Hlookup / Array fundtion or What? | Excel Discussion (Misc queries) | |||
| HLookup? or an array function?? | Excel Worksheet Functions | |||
| How do I find a value in an array (VLOOKUP? HLOOKUP?) | New Users to Excel | |||
Hybrid Mode
