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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
Hi Aladin, thanks for the highlight :p
Probably shall go for INDEX if the workbook is going to be large...
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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
Back
Top