2 levels lookup/index

[ivan5128]

Hi all

This is my first post here and im strugglin with this so-called 2 level lookup (i think)

lemme explain a bit. im goin to have a sheet 1 & 2
Sheet 1 is the sales of each fruit on each day (and obviously the table is much larger since it won't just contain 2 days)

Sheet 2 is the table i gonna do,

Saying everytime i input the date in Row 1 (ie B1, C1, D1...)
B2:B6 , C2:C6 will get the sales figure from Sheet 1 corresponding to the date,
while the "Others" would be the sum of the rest of the fruit

im thinkin of using an Index function, but just don't know how to work on it
Is there any way to work on this problem

Thanks alot

 

[Aladin Akyurek]

Try to fill in the date for every transaction. Then do the calculations.

 

[ivan5128]

Thanks, but if my sheet 1 getting larger (ie sayin i have a 1 year data, 365days), is there a way i can use a function instead of fill them manually?

 

[alvin-chung]

Hope this is robust enough for various scenarios...

	A	B	C
1		1/1/2013	2/1/2013
2	Apple	5	8
3	Banana	2	6
4	Orange	3	7
5	Pear	7	11
6	Melon	8	15
7	Others	51	29

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =IF(ISNA(MATCH($A2,OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,1,10),0)),SUM(OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,2,10))-SUM(B$2:B$6),SUMPRODUCT((OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,1,10)=$A2)*(OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,2,10)))) <tbody> </tbody>

<tbody>
</tbody>

Copy this formula to B2:C7

 

[Aladin Akyurek]

Hope this is robust enough for various scenarios...

[...]

=IF(ISNA(MATCH($A2,OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,1,10),0)),SUM(OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,2,10))-SUM(B$2:B$6),SUMPRODUCT((OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,1,10)=$A2)*(OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,2,10))))

<TBODY>
</TBODY>

Copy this formula to B2:C7

Try to read about volatility, which you compound with OFFSET, INDIRECT, and ADDRESS.

 

[alvin-chung]

Hi Aladin, thanks for the highlight
Probably shall go for INDEX if the workbook is going to be large...

 

[ivan5128]

Thanks for help! it works

Hope this is robust enough for various scenarios...

	A	B	C
1		1/1/2013	2/1/2013
2	Apple	5	8
3	Banana	2	6
4	Orange	3	7
5	Pear	7	11
6	Melon	8	15
7	Others	51	29

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B2 =IF(ISNA(MATCH($A2,OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,1,10),0)),SUM(OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,2,10))-SUM(B$2:B$6),SUMPRODUCT((OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,1,10)=$A2)*(OFFSET(INDIRECT(ADDRESS(MATCH(B$1,Sheet1!$A:$A,0),1,,,"Sheet1")),0,2,10)))) <tbody> </tbody>

<tbody>
</tbody>

Copy this formula to B2:C7

 

[alvin-chung]

You're welcome.
Just be aware that the if the workbook growing large the formula might take too much time to re-calculate (because OFFSET formula is volatile, as highlighted by Aladin)

 

[Aladin Akyurek]

You're welcome.
Just be aware that the if the workbook growing large the formula might take too much time to re-calculate (because OFFSET formula is volatile, as highlighted by Aladin)

INDIRECT, and ADDRESS too are volatile...