Hi

If there are not too much sheets, you could also try this one.

=LOOKUP("zzzz",CHOOSE({1,2,3,4},"",INDEX(Sheet3!$B $2:$B$12,MATCH(D5,Sheet3!$A$2:$A$12,0)),INDEX(Shee t2!$B$2:$B$12,MATCH(D5,Sheet2!$A$2:$A$12,0)),INDEX (Sheet1!$B$2:$B$12,MATCH(D5,Sheet1!$A$2:$A$12,0))) )