Returning Values in Cells via V&H lookups

I am using a Bloomberg API to retrieve information on certain stocks. The size of the sheet varies and hence the cells which I want to reference are dynamic. This can be seen in Sheet 1, Sheet 2 and Sheet 3. You can see the the Cloumns A,B,C,D which contain values are never in the same place. Hence I am having to change the reference of the cells everytime. Columns G will always be static and have a list of the stocks. In order to get the Consensus Rating(Column C with value in D) I can use a simple Vlookup for the stock.However for other variables(Column C) with values in Column D, I cannot autopopulate them. Is there any way around it? Thanks

If the placement of buys, holds, etc.. is consistent within column B, then try:

In H2:

=INDEX($D:$D,MATCH($G2,$A:$A,0)+COLUMNS($A$1:A$1)-1)

copied down and across the matrix..

since you skipped the currency, you will need to adjust the L2 formula to:

=INDEX($D:$D,MATCH($G2,$A:$A,0)+COLUMNS($A$1:F$1)-1)

and copy down

or you can hard code it like

IN H2:

=INDEX($D:$D,MATCH($G2,$A:$A,0))

in I2:

=INDEX($D:$D,MATCH($G2,$A:$A,0)+1)

in J2:

=INDEX($D:$D,MATCH($G2,$A:$A,0)+2)

in K2:

=INDEX($D:$D,MATCH($G2,$A:$A,0)+3)

In L2:

=INDEX($D:$D,MATCH($G2,$A:$A,0)+5)