HLookup, IF statement, multiple sort, somehow combine all of these
 

My file has multiple tabs in it - upwards of thirty. The first two tabs
contain data that the remaining tabs read from - approx 150 rows in total
between the two. Ideally the HLookup function would work best, but
unfortunately my source tabs (the first 2) are not sorted so that a HLookup
would work, and I can't sort them, so HLook up is out, SO.

I tried the IF statement, looking at the two source tabs one row at a time.
This pulls the information over correctly, but the information is spread over
150 rows, when only 5-6 rows contain data, SO

Then I thought I'd do a sort, so that the rows with the data appear together
at the top, but that would mean I would have to sort each tab (over 30 of
them) separately, everytime I update the source tabs. Don't want to do that.
I've tried grouping the sheets and performing the sort once (the area to be
sorted in each sheet is the same), but it wouldn't let me.

Can someone please help me?? Any ideas would be GREATLY appreciated!!