# Excel - lookup array based on cell value

##### New Member
I have workbook, containing 4 major worksheets named as
1. BNF WEEK
2. BNF NEXT WEEK
3. BNF MONTH
4. MAIN
Now I am working in sheet named as MAIN and I want formula where, if cell R6 = 1 than fetch data from 1st sheet i.e. BNF WEEK if R6 = 2 than fetch data from 2nd sheet i.e. BNF NEXT WEEK, and so on
Kindly help with this issue,
Currently using below formula
=INDEX('BNF WEEK'!\$E\$2:\$E\$500,MATCH(\$J10+L\$5&L\$7,'BNF WEEK'!\$XER\$2:\$XER\$543,0))*-L\$6
Any help highly appriciated

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=INDEX(CHOOSE(R6,'BNF WEEK'!\$E\$2:\$E\$500,'BNF NEXT WEEK'!\$E\$2:\$E\$500,'BNF MONTH'!\$E\$2:\$E\$500,Main!\$E\$2:\$E\$500),MATCH(\$J10+L\$5&L\$7,CHOOSE(R6,'BNF WEEK'!\$XER\$2:\$XER\$543,'BNF NXT WEEK'!\$XER\$2:\$XER\$543,'BNF MONTH'!\$XER\$2:\$XER\$543,Main!\$XER\$2:\$XER\$543),0))*-L\$6``

Replies
1
Views
239
Replies
2
Views
118
Replies
0
Views
115
Replies
3
Views
152
Replies
0
Views
369

1,141,073
Messages
5,704,138
Members
421,328
Latest member
mippy

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