Easier Match/Offset?

[Guest]

Is there an easier way to do this?

Basicly the Match is looking at a specific field in A12, which needs to be
dynamic as I'm pasting this formula down. Once the field matches, it will
ofset it based on its Cell Row #-6 rows, then I need it to calculate I21 *the
matching row/cell reference. I21 needs to offset based on column. It looks
like a mess, but seems to be doing the job. But I'm sure others in the
office may get lost, was wondering if there was somethign easier to do.

=(OFFSET(I21,0,0)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))+(OFFSET(I21,0,1)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))+(OFFSET(I21,0,2)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))+(OFFSET(I21,0,3)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))

 

[Biff]

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=SUM(I21:L21*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))

Biff

 

[Biff]

Or, normally entered (not an array):

=SUMPRODUCT(I21:L21*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))

Biff

 

[Guest]

Not quite, its not moving over a column for each instance between I and S.
This is my final formula.

=(OFFSET(I21,0,0)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,0))+(OFFSET(I21,0,1)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,1))+(OFFSET(I21,0,2)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,2))+(OFFSET(I21,0,3)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,3))+(OFFSET(I21,0,4)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,4))+(OFFSET(I21,0,5)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,5))+(OFFSET(I21,0,6)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,6))+(OFFSET(I21,0,7)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,7))+(OFFSET(I21,0,8)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,8))+(OFFSET(I21,0,9)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,9))+(OFFSET(I21,0,10)*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,10))

 

[Biff]

That doesn't look anything like the formula you posted before! <g>

Is the one you just posted the final *FINAL* formula? <bg>

=SUMPRODUCT(I21:S21*OFFSET($I$6,MATCH(A12,$H$1:$H$8,0)-6,{0,1,2,3,4,5,6,7,8,9,10}))

Biff

 

[Guest]

Wow Biff, looks like it would do the job, that was my final formula, its
working, but yours looks like a great improvement. Unfortunately, I'm
getting a #Value. Thanks for your help Biff, I dont want to bother any more,
I'll leave it as is. Unless you feel challenged LOL

 

[Biff]

Unless you feel challenged LOL

I love a challenge!

This would be easy to figure out if I could see what it is I'm trying to
figure out but I can only try to recreate the situation based on the
information I'm getting from your formula. So, try this:

=SUMPRODUCT(I21:S21*OFFSET(I6:S6,MATCH(A12,H1:H8,0)-6,,,11))

Biff

 

[Guest]

I'm still in shock, I think that worked, let me fool around with it and make
sure its fool proof. Wow. BRB

 

[Guest]

Wow, it works, it works. all it was was that 11 in Width, I didnt even think
of looking into that option. Amazing. Everyone, listen up, Biff is the
Mannnnn! Thanks Biff. I knew there was a way to unnest that monster of a
forumla I had.

 

[Guest]

One more thing, just made some references absolute.

=SUMPRODUCT(I21:S21*OFFSET($I$6:$S$6,MATCH(A12,$H$1:$H$8,0)-6,,,11))

 

[Guest]

Added the COLUUMN formula to make it a bit more dynamic in case columns are
added between the I6 and S6. As you can see I already did, its up to T now.
This is an interesting formula. Not bad. Working on any possible kinks.

=SUMPRODUCT(I19:T19*OFFSET($I$6:$T$6,MATCH(A10,$H$1:$H$8,0)-6,,,COLUMNS($I$6:$T$6)))

 

[Biff]

Wow!

Thanks for the "enthusiastic" feedback!

The thing that was throwing me off was MATCH(.......)-6 and trying to figure
out how -6 related to your data setup.

Biff

 

[Guest]

The six is there as a row reference, I could have used Row(), but since its
never going to change, I left it as 6.