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?

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?

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?