Excel - lookup array based on cell value

[adpatel]

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

 

[Fluff]

How about

=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