# 2 levels lookup/index

#### ivan5128

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

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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

#### ivan5128

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

<tbody>
</tbody>

<tbody>
</tbody>

Copy this formula to B2:C7

Last edited:

##### MrExcel MVP
Hope this is robust enough for various scenarios...

[...]

<TBODY>
</TBODY>

Copy this formula to B2:C7

#### alvin-chung

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

#### ivan5128

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

<tbody>
</tbody>

<tbody>
</tbody>

Copy this formula to B2:C7

#### alvin-chung

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

##### MrExcel MVP
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...

Replies
9
Views
303
Replies
5
Views
880
Replies
5
Views
130
Replies
3
Views
516
Replies
1
Views
726

1,171,465
Messages
5,875,666
Members
433,147
Latest member
Nav05

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

### Which adblocker are you using?

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

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