INDEX MATCH MATCH with Dynamic INDEX

[bigjdawg43]

Hi,

This will be more clear once the attachment is viewed, but I have a raw sheet I receive from a business partner on a regular basis. The sheet is constructed in such a way where there are essentially two types of metrics that are on the same tab and changing the structure/format of the sheet is not an option (although that would answer my question).

The idea here is that I'm trying to lookup metrics from the second part of the table, not the first part of the table. As an example, I'd like to find Stock turnover for the target in the attached. As such, I'd want to display 40%, which corresponds to Stock Turnover, NOT 50%, which corresponds to a different metric. All the other figures I try to pull in the first part of the table work fine with INDEX MATCH MATCH, but in this case that doesn't work because the number corresponding to the first part of the table will be displayed instead of the second part.

I've also tried using OFFSET MATCH MATCH, but that doesn't seem to work because I'd need to use a formula to find "Current Portfolio" cell as my starting point and offset from there, and it seems OFFSET can't use a formula as a cell reference (keep in mind the column for "Current Portfolio" is dynamic).

One of my thoughts was to alter the INDEX reference to begin with the "Current Portfolio" column (requiring a formula to find that column in the first place), but when I tried doing that, I seemed to get errors.

In any case, any help appreciated.

[leelnich]

Can you MATCH "turnover" in row 2, rather than "Target" in row 1? Or just index column H specifically, if that's a known range?

[TFiske]

Hi,
it seems OFFSET can't use a formula as a cell reference (keep in mind the column for "Current Portfolio" is dynamic).

No, but it can use another named range.

I created two named ranges, Current_Portfolio_Column_Find:

Please Login or Register  to view this content.

Which find the column with the value "Current Portfolio" in row one and returns that cell address, in you're example E1.

Then this named range can be used as the reference argument in another =OFFSET for Current_Portfolio_Range:

Please Login or Register  to view this content.

Which is all cells in the column labeled Current Portfolio in you're example. Does that help?

I don't quite see where you are getting this 40% from as the sum of the Current_Portfolio_Range is 22.4238. But it seemed from you're OP that this might help. At least it should address your dynamic column problem.

Help me understand the rest of you're issue...or if I even addressed you're problem to begin with. Thank.

[TFiske]

The idea here is that I'm trying to lookup metrics from the second part of the table, not the first part of the table. As an example, I'd like to find Stock turnover for the target in the attached. As such, I'd want to display 40%, which corresponds to Stock Turnover, NOT 50%, which corresponds to a different metric. All the other figures I try to pull in the first part of the table work fine with INDEX MATCH MATCH, but in this case that doesn't work because the number corresponding to the first part of the table will be displayed instead of the second part.

Do you want the cell at O2 to show 40%?

Leelnich's approach from post #2 would work. If the columns are dynamic you could use a similar approach to my post above and create a dynamic range that would find the column with "Turnover" in row 2 and then Offset down from there:

Turnover_Column_Find:

Please Login or Register  to view this content.

Then to find the value at Stock Turnover:

Please Login or Register  to view this content.

[bigjdawg43]

Apologies, maybe I should have explained better. To be clear, there is no formula in stock turnover. It's a number in that cell calculated by my vendor, but does not include any formulas. I didn't include it here, but basically I'm looking for that number to populate another sheet. That's why I'm wondering about these various "lookup" type formulas. Thanks so much for your help, i really do appreciate it. So i had a couple questions, because I'm not all that familiar with using OFFSET with dynamic ranges...

1) Could the "Turnover Column Find" named range be included in the actual OFFSET formula to find Stock Turnover? Or, because this is by definition a named range, I'd be forced to calculate this in two separate steps (like you mentioned, one named range and one that uses the named range to find Stock Turnover).
2) To be clear, OFFSET cannot utilize a formula as its first argument, but CAN utilize a named range?
3) In the above examples, you're basically creating a named range, but that named range returns a specific cell reference. From there you use OFFSET to find the correct figure. Conceptually, is that right?
4) I'm probably reaching a little bit here, but is there a way to do this that doesn't involve named ranges that would be tied up in one formula?

Thanks so much again.

[TFiske]

1) Yes, if you look in my previous post the named range Turnover_Column_Find is used as the reference argument for the OFFSET formula in cell O3.

2) Correct, the OFFSET function requires a specific cell or range address so it knows where to start from. A formula, in and of itself is not a cell address, i.e. A1. You can largely think of Excel as a coordinate grid, the OFFSET functions arguments are:

Reference --- the starting point, say A1 for example
Rows --- move y number of rows down (or up if y is negative)
Lets say we use 2 for this part, now were at cell A3
Colums --- move x number of columns right (or left if x is negative
Lets say we use 2 for this part, now were at cell C3
Height --- from cell C3 include a range that is 'n' cells down (or up if 'n' is negative), lets say we use 2 so now we have the range C3:C4
Width --- from cell C3 include a range that is 'n' cells wide, lets say 2 again, so our range is now C3:D4

You can use formulas to calculate any of these arguments because they just need to be whole numbers, the reference argument must be a cell or range of cells. Since a named range is just that, it can be used as the reference argument.

3) Correct. Since the first Offset function is being used to return a dynamic cell reference that I call Turnover_Column_Find, that named range can be used as the reference argument in another Offset. When the first Offset changes, the 2nd will aswell.

4) Probably, theres any number of ways to accomplish any given task in Excel. But I think youre better off with dynamic named ranges. It sounds like the structure of your data changes frequently. If I understood alittle more about youre end goal Id have a better answer, Im still unclear on that part.

In thr toolbar, click on Formulas>Name Manager, you'll see a list of all the dynamic ranges I set up and their formulas. If you click inside the formula bar of the Name Manager, Excel will highlight the range produced by that formula.

[AliGW]

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[TFiske]

Appologies, responding from a cell phone. I thought I trimmed it down. Will erace the auto-quoted part in the future when responding from my phone.

[AliGW]

Just type into the quick reply box.

[bigjdawg43]

I should add a couple things to this that I may have forgotten to mention.

First, there are a bunch of columns after the current portfolio column. so in this case, that's column F but it could move around. So i think the formula with the Current Portfolio Column Find named range would conceptually do the job there in terms of finding a starting point from which to use OFFSET.

Second, and this is what I may have forgotten to mention, is that the "Target" column will ALSO be dynamic. Again, there are a number of columns in between that I've simply removed. In this case, the Target column is two columns from the Current Portfolio column, but it could be 4 columns over or more. The point is that not only is the Current Portfolio column dynamic, but the Target column is also dynamic, which I would think would affect the OFFSET that uses the named range. So O5 has the correct answer using the named range, but the OFFSETs are hard coded and need to be dynamic. I know that to make them dynamic we need a MATCH, but my concern (or lack of understanding) comes in when trying to enter the MATCH array, but only for the Current Portfolio column and beyond that point.

[bigjdawg43]

I think my last post was a little more clear in terms of the end goal, but if it wasn't, the end goal is to use the named range/OFFSET combination to find the Current Portfolio column as a starting point and from there look to use OFFSET MATCH MATCH (or other method) that will find me specific metrics in specific columns after the Current Portfolio column has been located.

The trick, though, is that when I look for these metrics/values, the lookup array must be from the Current portfolio column (found by using the named range/OFFSET combination) and beyond. If it's not for some reason, the metric captured will probably end up being the one listed in the first part of the sheet (BEFORE the Current Portfolio column) which I don't want.

Hopefully that helps a little.

[TFiske]

Ok, first of all, the OFFSETS in my example are not hard coded. They are dynamic, but they may not be as dynamic as you need them to be.

My original OFFSET formulas use MATCH to determine which column to look for. I.E.: Current_Portfolio_Column_Find is based on the formula:

Please Login or Register  to view this content.

The column argument of the OFFSET function is calculated with a Match formula that looks for the value "Current Portfolio" in row 1, then subtracts 1 because counting starts at 0. That way, no matter where the "Current Portfolio" column is located, the Current_Portfolio_Column_Find and Current_Portfolio_Range named ranges will ALWAYS find the right column as long as that column always has the value "Current Portfolio" in Row 1.

In the attached example I have added a second sheet called "Output." I have created the following dynamic ranges:

Metrics:

Please Login or Register  to view this content.

Headers_Find:

Please Login or Register  to view this content.

Headers:

Please Login or Register  to view this content.

Data_Array:

Please Login or Register  to view this content.

Then I can used these ranges in a series of INDEX(MATCH) formulas in the Output Tab.

The Index(MATCH) is as follows:

Please Login or Register  to view this content.

Given 1 small change, that the first instance of "Target" in row 1 is changed to "Target1" and the second instance of "Target" in row 1 is changed to "Target2", this method will copy ALL VALUES from your initial sheet regaurdless of how many rows/columns are in the initial sheet. The dropdown menus for Column A and Row 1 of 'Output'! will automatically update aswell. See the row label "TEST ROW TFISKE" and Column Header "TEST COLUMN TFISKE".

This allows you to customize the Output to only those headers and row labels you want.

Where do you need to go from here?

[TFiske]

I think I can adapt this method for 2 instances of "Target" in row 1 with the SMALL function. I will get back to you.

[leelnich]

Here's a different approach based on the non-volatile INDEX function:

Please Login or Register  to view this content.

Notes:
The above formula uses 1 dynamic named range (shown here with the worksheet qualifiers stripped out to make it readable):

Please Login or Register  to view this content.

To improve calculation speed, I avoided full-column/row references and set the last cell at 500 rows/500columns. I assume this is big enough to cover foreseeable data.

[bigjdawg43]

Thanks for putting the attached together. For additional background (I may or may not have mentioned this), these metrics are actually being displayed on another sheet (effectively your "Output" tab), so i think we're going in the right direction. There are a number of other portfolios in the real sheet as well (30%, 40%, 50%, 60%, etc.. ) but my expectation would be that as long as these named ranges cover the entire first row, this won't be an issue.

That said, there's one more question I had regarding your attachment with the Output sheet. The "Target" header represents the same portfolio, just different metrics based on which side of "Current Portfolio" column it falls. So, on your attached, there are two instances of Target (Target1 and Target2) which makes sense, but these are the same portfolio. As such, any metric listed for Target2 should actually be placed in the same area as it is for Target1 (along with any other portfolios in the dropdown. So for example, in at the bottom of the Current portfolio column, you see Number of Trades, Stock Turnover and Minimum Trade size. Those metrics (along with a few others) will always be at the bottom of the Current Portfolio column. But for this to be effective, I would want the labels in column D of your output to actually go in column A with the rest of the labels. So the number that represents Stock Turnover for Target2 in column H would actually belong in column B with the rest of the Target metrics (with the "Stock Turnover" label in column A). Same thing with other portfolios (the post Current Portfolio columns are basically a mirror of the pre current portfolio columns). But if i could choose the different portfolios and have all the appropriate metric labels line up in column A with their appropriate metrics in column B for the different portfolios, that would be perfect and I believe exactly what I'm looking for.

[bigjdawg43]

It's late and I'm realizing my last post may not have made all that much sense so I'll try to summarize a couple more points, using the Target portfolio in particular (and realizing that on the original sheet many more portfolios exist).

1) The output tab is great. Since it works off a drop down it is perfect for me being able to choose different variations of portfolios, because there are far more than what I've described in the example file (usually 10%-90%, plus labels for "Target", "Min", and "Max"). Remembering that before current portfolio and after current portfolio are essentially mirrors of each other, Ideally the goal would be to have all the metrics for the same portfolio in the same column. So for example, I'd like to see all the metrics in column A show up on the output tab in addition to all the metrics at the bottom of the Current Portfolio column (in this case, K20-K22) in one long list.

2) I'm not sure if i was clear about this or not, but as you can see from 1) different metrics are coming from different parts of the table such that column A metrics would come from the array before Current Portfolio column and metrics at the bottom of column K would come from the array after the Current Portfolio column. Not sure if that was clear so I wanted to emphasize once more.

Thank you so much again.

[bigjdawg43]

All that said, the named range wouldn't need to include both the pre and post current portfolio section because they'd be the same thing

[AliGW]

Why don't you just provide a manual mock-up of what you are after?

[bigjdawg43]

It's late and I'm realizing my last post may not have made all that much sense so I'll try to summarize a couple more points, using the Target portfolio in particular (and realizing that on the original sheet many more portfolios exist).

1) The output tab is great. Since it works off a drop down it is perfect for me being able to choose different variations of portfolios, because there are far more than what I've described in the example file (usually 10%-90%, plus labels for "Target", "Min", and "Max"). Remembering that before current portfolio and after current portfolio are essentially mirrors of each other, Ideally the goal would be to have all the metrics for the same portfolio in the same column. So for example, I'd like to see all the metrics in column A show up on the output tab in addition to all the metrics at the bottom of the Current Portfolio column (in this case, K20-K22) in one long list.

2) I'm not sure if i was clear about this or not, but as you can see from 1) different metrics are coming from different parts of the table such that column A metrics would come from the array before Current Portfolio column and metrics at the bottom of column K would come from the array after the Current Portfolio column. Not sure if that was clear so I wanted to emphasize once more.

I've also attached the actual original sheet so that you can see what I'm talking about regarding the number of portfolios and the mirror image. If the output tab created earlier can be modified to capture these entire ranges I think it would be perfect.

Thank you so much again.

[bigjdawg43]

Editing this post because I can't seem to delete it.

[bigjdawg43]

Editing this post because I can't seem to delete it.

[bigjdawg43]

Editing this post because I can't seem to delete it.

[bigjdawg43]

Editing this post because I can't seem to delete it.

[xlnitwit]

Hi,

Can you post one workbook that shows both an example of the source table and an example of the sort of output you want?

[bigjdawg43]

Sure, the attached uses a more comprehensive version of the source sheet with an Output tab taken from a fill TFiske created earlier in the thread. I think if that table was adjusted for use with a much larger sheet (my test data was originally just a snippet of the original) and moving the metrics found in the "Current Portfolio" column to reflect in column A along with the others. The key point is that the source file has two versions of the array of portfolios that are mirrors of each other and I'm trying to move the metrics in the second half of the sheet to be shown with the metrics/labels in column A on the output tab. The source sheet changes as well, so the height varies, width varies, and "Target" column varies.