heather wrote:
Help! Trying to refernce more cells but this time in different sheets. I have
revamped the formula but i can't figure it out here is sample..
http://www.savefile.com/projects/808732916


I think this is what you are looking for:

=IF(A3=A2,INDEX(INDIRECT("Sheet2!"&ADDRESS(MATCH(E 2,Sheet2!$B$1:$B$40,0)+1,2)&":$B$40"),
MATCH(A3,INDIRECT("Sheet2!"&ADDRESS(MATCH(E2,Sheet 2!$B$1:$B$40,0)+1,1)&":$A$40"),0)),
INDEX(Sheet2!$B$1:$B$40,MATCH(A3,Sheet2!$A$1:$A$40 ,0)))