I am looking to for a formula that will lookup across multiple tabs. I have done something very similar using the following formula:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!J:J"),$A5,INDIRECT("'"&Tabs&"'!L:L")))

using the above as an example for what i am trying to accomplish 'Tabs' are the tab references I am looking at; A5 is the reference number i am looking up, Column J is the list of reference numbers in every tab, Column L is the string of text I want to pull out.