Excel 2007 : SUMPRODUCT() partial criteria lookup using LEFT() and INDIRECT() - Fails to Load Range

Hello!

So, here is the scenario:
I've made a series of formula that rips apart a pdf file that is copied into a column.
A pivot table then summarizes this data into an easy to read format.
The following is a formula which uses the column (lot) and row (description) headers to locate a receipt number to go against them. The receipt number is the unique identifier, however there will only ever be one instance of lot/description so I am able to locate this using a multiple condition sumproduct.

Please Login or Register  to view this content.

Its a mess at the minute.
This worked perfectly until I found that sometimes the lot number will be classed as lot*1, meaning all of lot, lot*1 and lot*2 - the only time that a "*" appears in the header only once.

The problem is the left() on the indirect. Following the calculation steps, it appears that instead of trying to perform that function on each cell in the range, it chooses the first cell and returns a blank as the data I am referencing is in about row 20000, but can vary.

I'm not sure if I have some syntax wrong, or I am over complicating the situation, but any help would be appreciated!

Many thanks,
Monobi