Did you try it? Normal lookups won't work because of the gaps in the data.
This method is general and can be extended to any number of columns
by replacing column references by the lookup formulas shown. You need
to make the ranges long enough to allow for more data after refresh
however and it does make for long formulas,

A simpler alternative for returning values in the data region of the table
is GetPivotData. You can create this by pressing = and clicking a cell in
the table and then editing the criteria. This agrees with the result above:

=GETPIVOTDATA("Total Lots (Cleared)",$A$1,"Instrument Code",E3,
"Ledger Code(Query 1 with Trading)",F3)



But if the blanks are going to vary on a day to day basis would this
work. The pivot table is big and I think this way might be too manual
as there are lots of lookups etc etc.

Thanks