Recap Data

[stormania]

Dear excell master

Kindly need your help that base on data (attach file) how to change data to recap (sheet recap)?
Thanks in advance

 

[bosco_yip]

In B4, formula copied across and down :

=OFFSET(data!$A$1,MATCH($A4,data!$A$1:$A$100,0)+MATCH(B$2,data!$A$1:$A$100,0)-3,MATCH(B$3,data!$B$3:$C$3,0))

or,

=VLOOKUP(B$2,INDEX(data!$A:$A,MATCH($A4,data!$A:$A,0)):data!$C$1000,MATCH(B$3,data!$A$3:$C$3,0),0)

 

[stormania]

formula its work, but i have question
MATCH(B$2,data!$A$1:$A$100,0)-3 ==> what is the meaning -3 in that formula ?

 

[bosco_yip]

=MATCH($A4,data!$A$1:$A$100,0)+MATCH(B$2,data!$A$1:$A$100,0)-3

It is to calculate the Offset row number from the starting reference A1

Regards

 

[stormania]

=MATCH($A4,data!$A$1:$A$100,0)+MATCH(B$2,data!$A$1:$A$100,0)-3

It is to calculate the Offset row number from the starting reference A1

Regards

Thanks alot..for your solution

 

[Peter Bartholomew]

This far, it is just a case of returning values from a column by index, no lookups are needed.
I used a named formula 'k' to create the table of indices (shown on the worksheet for info only)
= SEQUENCE(1, numPeriods) + offset*SEQUENCE(numCodes, ,0)
The table is then
= INDEX( Actual, k )



The value, 9, of 'offset' is the spacing between tables.