So I have worked out a basic solution, it requires me to have it filted in a list.

=OFFSET((INDIRECT("'"&$G1&"'!B$2")),MATCH(B2,CATAGORY,0)-1,-1,COUNTIF(CATAGORY,B2),1)

using this website

http://www.contextures.com/xlDataVal13.html