# Recap Data

#### stormania

##### Member
Dear excell master

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

#### Attachments

• 16.3 KB Views: 7

#### bosco_yip

##### Excel Ninja
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)

Last edited:

#### stormania

##### Member
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

##### Excel Ninja
=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

##### Member
=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

#### Peter Bartholomew

##### Well-Known Member
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.