One way:

=LOOKUP(B1,INDIRECT("'" & C1 & "'!B4:B34"),INDIRECT("'" & C1 &
"'!E4:E34"))

Note that the single quotes are only necessary if C1 may have a space in
the text.


In article ,
"Mike Punko" wrote:

ok I have a workbook with sheets named by month (Sep, Oct, Nov...) I have a
sheet named Weekly. In Weekly I have a LOOKUP function as such.

=LOOKUP(B1,Oct!$B$4:$B$34,Oct!$E$4:$E$34)

B1 = Date in Weekly to lookup.
Oct! = the sheet to look in.
I'm looking for a way to have the Oct referance be determined via cell
referance. I tried some codes but just can't get this to work. I know I
would do a

TEXT(B1,"mmm") to get "Oct" trouble is setting up my original code to do
this. I've also been tryingto do it with INDIRECT function but to also no
result. I know it's just a wrong " or , somewhere.