Dynamic INDEX/MATCH

The attached document has a column (here column R) that can change. At the bottom of the sheet (rows 1761-1766), in that column, there are some labels I'm hoping to use for an INDEX/MATCH, vLOOKUP type formula. The formula itself will be on another sheet (on the attached, Sheet2!B8 for example. What I'm wondering is how to basically use INDEX/MATCH or VLOOKUP, with the array beginning in the column where i find "Current Portfolio" (in this case, again, column R). I can't seem to wrap my head around how this should be done. Thanks!

Sorry about that. Yes, you are correct for #1. The "Current Portfolio" column is dynamic and has the ability to change.

As far as #2 is concerned, apologies for leaving that part out. Your assumption was essentially correct. Except that this feeds to a different sheet where the user (me) will put in the reference (Min, 30%, Target%, etc...) so I'd need to lookup based on that one reference. And yes, you're also correct in that the Min column (which is essentially the beginning of the array in which I'd like to search) will always be two columns to the right of "Current Portfolio" tab.

I'm hoping that's more clear but if it's not please let me know. And thanks for the help, much appreciated!

Any ideas?

I've attached another copy of the test workbook that includes another metric I need to find along with three example references where I'm trying to show that I would need metrics for certain portfolios at certain times, with the flexibility to change those references. That's why initially, i was thinking some form of INDEX/MATCH or VLOOKUP. Effectively, I need a way to find the "Current Portfolio" tab and then use that as the first column of a new array in which I will look to find the appropriate values on the attached sheet.

This is great, and almost exactly what I'm looking for. To be honest, I'm not fully understanding what the formula is actually doing (I'm not sure about the role of the sumbroducts and multiplication by 10), but it does seem to be working. There seems to be only one issue that I'm having which is the portfolios that are described by text (Min, Target, and Max). Target populates on the sheet as it is in the version you attached, but if I change that reference cell to something else, and then back to Target, I get an error. Same thing with when "Min" and "Max" are entered.

Thank you so much for the help!