2 levels lookup/index

ivan5128

New Member
Joined
Jan 7, 2014
Messages
3
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)
tMIwcLb.jpg

Sheet 2 is the table i gonna do,
3GAbFIZ.jpg


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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

ivan5128

New Member
Joined
Jan 7, 2014
Messages
3
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

Active Member
Joined
Nov 24, 2013
Messages
361
Hope this is robust enough for various scenarios...

ABC
11/1/20132/1/2013
2Apple58
3Banana26
4Orange37
5Pear711
6Melon815
7Others5129

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

Active Member
Joined
Nov 24, 2013
Messages
361
Hi Aladin, thanks for the highlight :p
Probably shall go for INDEX if the workbook is going to be large...
 

ivan5128

New Member
Joined
Jan 7, 2014
Messages
3

ADVERTISEMENT

Thanks for help! it works ;)
Hope this is robust enough for various scenarios...

ABC
11/1/20132/1/2013
2Apple58
3Banana26
4Orange37
5Pear711
6Melon815
7Others5129

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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

Active Member
Joined
Nov 24, 2013
Messages
361
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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...
 

Forum statistics

Threads
1,137,062
Messages
5,679,392
Members
419,825
Latest member
MegastarMagus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top