Is this possible - type of Vlookup dynamic range

Hi,

I am trying to find a way to automate the pulling of data from multiple ranges into one sheet from two different files. I have attached an example and will try to explain it and was wondering if this is even possible.

In the example the 3 BB report tabs represent different outputs from Bloomberg in a standard report. The starting cell is always the same, but the number of positions in the portfolio varies. Also, when I rebalance the portfolios the same portfolio can increase or decrease in the number of holdings (portfolio one could have 8 next time)

The portfolio tab represents the current holdings in each portfolio.

The consolidated tab represents a report I am trying to generate by automatically pulling in data.

The issue that I am having is that multiple portfolios have the same security, but in a different weight % of the portfolio.

Is it possible to create a vlookup (or some other function/nested function) to search for the portfolio name and create a range from that? I can't highlight a whole column and do a dynamic range that way because the generated reports from Bloomberg have merged cells that interfere with doing a vlookup.

if this doesn't make sense and you need for clarifiing or explained different let me know.

Thank you for the help.