• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recap Data

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)

70968
 
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
 

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 )

70974


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