Using offset function-How to do

[Amol lad]

In Data sheet cell K2 I want to update sales Amt of the Product by its category.
How Can i able to do it by the offset formula?
I knw we can do this by index match.

But can we able to use the offset formula in it??

Assuming the data at the leftmost side is an Pivot table data.

Please tell me???? How we can do this by using offset function????

 

[Amol lad]

Cell k3

 

[Khalid NGO]

Hi,

I knw we can do this by index match.

Can you share your index/match formula?

Regards,

 

[Khalid NGO]

You can use some amazing formulas given here:
http://chandoo.org/forum/threads/formula-challenge-021-sumif-in-merged-cells.11927/

for example this {array formula} in K3:

=SUM((LOOKUP(ROW($A$3:$A$26),ROW($A$3:$A$26)/($A$3:$A$26<>""),$A$3:$A$26)=$J3)*($B$3:$B$26=K$2)*$C$3:$C$26)

{array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}

Regards,

 

[cbon]

Here is an offset formula, except you are better off using the index match. Offset is very static; nothing can change without tending to the formula.

K3 = OFFSET($A$2,MATCH($J3,$A$3:$A$26),2,,)

L3 = OFFSET($A$2,MATCH($J3,$A$3:$A$26)+1,2,,)

M3 = OFFSET($A$2,MATCH($J3,$A$3:$A$26)+2,2,,)

Kind Regards,

 

[deciog]

My contribution

Khalid NGO, excellent solution, but for non-matrix formula, exchange SOMA for SOMARPRODUCT.

Decio

 

[Khalid NGO]

Hi Decio,

Credit goes to our experts Haseeb A, Lori & Shiri.
I just adjusted the range and added one condition.

exchange SOMA for SOMARPRODUCT.

I got it right? replace SUM with SUMPRODUCT

Regards,

 

[Khalid NGO]

K3 = OFFSET($A$2,MATCH($J3,$A$3:$A$26),2,,)
L3 = OFFSET($A$2,MATCH($J3,$A$3:$A$26)+1,2,,)
M3 = OFFSET($A$2,MATCH($J3,$A$3:$A$26)+2,2,,)

Hi,

We can add a COLUMN function to make single formula:
=OFFSET($A$2,MATCH($J3,$A$3:$A$26)+COLUMN(A1)-1,2,,)

Regards,

 

[deciog]

Khalid NGO

Sorry, I did it in Portuguese formula.


Decio

 

[cbon]

Hi,

We can add a COLUMN function to make single formula:
=OFFSET($A$2,MATCH($J3,$A$3:$A$26)+COLUMN(A1)-1,2,,)

Regards,

Yes, makes it more dynamic!

 

[Marc L]

Hi !

Sorry, I did it in Portuguese formula.

In any local langage Excel version, any cell formula can be seen
in native english : select formula cell, go to VBE side (Alt F11),
in Immediate window (Ctrl G) enter ?activecell.formula
and hit Enter key, that's it !
​

 

[Amol lad]

Hi,



Can you share your index/match formula?

Regards,

see the bellow formula

=INDEX($C$3:$C$26,MATCH($J3,$A$3:$A$26,0)+MATCH(K$2,$B$3:$B$5,0)-1)

 

[cbon]

Khalid NGO

Sorry, I did it in Portuguese formula.


Decio

I understood!

 

[Amol lad]

Dear Khalid NGO

Thank You your trick is very awesome.

=SUMPRODUCT((LOOKUP(ROW($A$3:$A$26),ROW($A$3:$A$26)/($A$3:$A$26<>""),$A$3:$A$26)=$J3)*($B$3:$B$26=K$2)*$C$3:$C$26)


but will you tell me can we use vlookup???

 

[Khalid NGO]

Hi @deciog
No problem dear.

can we use vlookup???

Hi,

Every function have different syntax and are made for different requirements. Each formula works differently. It is not necessary to get answer with all available functions.

You already have various solutions.

Regards,

 

[Amol lad]

Hi @deciog
No problem dear.



Hi,

Every function have different syntax and are made for different requirements. Each formula works differently. It is not necessary to get answer with all available functions.

You already have various solutions.

Regards,

yes you are right...

Thank You.

 

[bosco_yip]

Dear Khalid NGO

Thank You your trick is very awesome.

=SUMPRODUCT((LOOKUP(ROW($A$3:$A$26),ROW($A$3:$A$26)/($A$3:$A$26<>""),$A$3:$A$26)=$J3)*($B$3:$B$26=K$2)*$C$3:$C$26)


but will you tell me can we use vlookup???

1] Khalid NGO's formula :

=SUMPRODUCT((LOOKUP(ROW($A$3:$A$26),ROW($A$3:$A$26)/($A$3:$A$26<>""),$A$3:$A$26)=$J3)*($B$3:$B$26=K$2)*$C$3:$C$26)

2] If LOOKUP change to VLOOKUP function, it will be an array formula and become :

=SUM((VLOOKUP(N(IF({1},ROW($A$3:$A$26))),IF({1,0},ROW($A$3:$A$26)/($A$3:$A$26<>""),$A$3:$A$26),2,1)=$J3)*($B$3:$B$26=K$2)*$C$3:$C$26)

p.s. : {array formula needs to be entered with a key combination of Ctrl+Shift+Enter}

Regards
Bosco