search array of cells in one workbook and return the value of the adjacent cell

Hi

I'm not sure that you need VBA to achieve what you want.
Are you aware of the GetPivotData function?

Take a look at the attached example, where I have tried to create on a single sheet something similar to your description.
There is a table of data in columns A:B which are then summarized with a simple Pivot Table, located at F3
In column J:K is a report, which contains just some of the headings that appear in the PT.

Using the GetPivotData function, if you were to place your cursor in cell K4 and type an = and then point to the value you want to extract from the PT, say cell G2, then you would get
=GETPIVOTDATA("Value",$F$3,"Type","Loop2")

If you now change that formula, so that the Type is not referring to "Loop2", but instead refers to the heading that is in J4, you would have
=GETPIVOTDATA("Value",$F$3,"type",$J4)

This can then be copied down column K of your report, and it will extract the PT values that match the headings that have entered in column J.

Hi

Well you can do it directly from the PT by using GetPivotadata twice

=GETPIVOTDATA("Value",$F$3,"Type","Loop1")+GETPIVOTDATA("Value",$F$3,"Type","Loop2")

Alternatively, you could pick it up from your final Results Table with Index and Match
=SUM(INDEX(K:K,MATCH($N$3,J:J,0)):INDEX(K:K,MATCH($O$3,J:J,0)))
where N3 holds the first value e.g. Loop1 and O3 holds the second value - Loop2

I have shown an example on the attached workbook

Hi

Take a look at the attached workbook, maybe it is more like your data as I have added a Month column to the source, and I have some months with no data for the loops.

Taking Months to the Report Filter area and selecting an individual month, the standard layout of the Pt would just show the Loops that have data in that month.
However, if you right click on the PT and select Field Settings, and then the Layout & Print tab, you will see an option to show Items with No data.
Select this, and then your summation will work, using either multiple GetPivotData functions, or using the Index method that I showed previously.

If this doesn't solve your problem, perhaps you could upload a sample of your data, PT and report (all on a single sheet as I have done, rather than in separate workbooks)