Indexing with mutiple results - dynamic source

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Thanks to picking the brains of the good folks on this board through searching previous threads, I was able to come up with a formula that returns multiple results when indexing rather than just the first match.

Now the problem I have is that my source table is going to vary in size. My source table in the formula I got working below is a dynamically named range 'ImportedData'. The data in this table will always start on Row 8 but the last row of the table will vary, so the bold/red areas of the formula need to be dynamic.

Can anyone suggest how I might modify this formula to allow for the dynamic table size?

Rich (BB code):
=IF(ROWS(B$19:B19)<=L$19,INDEX('Empower Data'!$D$8:$D$19,SMALL(IF('Empower Data'!$F$8:$F$19=E$17,ROW('Empower Data'!$D$8:$D$19)-ROW('Empower Data'!$D$8)+1),ROWS(B$19:B19))),"")

Any help is appreciated.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Could you maybe replace 'Empower Data'!$D$8:$D$19 with the name of your dynamically named range?
 
Upvote 0
Wish it were that simple. Doing so provides a #Ref! error.

Maybe there is some syntax to define the lookup column in the named range and also the column in the named range for the return value, but I haven't found it yet.
 
Upvote 0
Thanks to picking the brains of the good folks on this board through searching previous threads, I was able to come up with a formula that returns multiple results when indexing rather than just the first match.

Now the problem I have is that my source table is going to vary in size. My source table in the formula I got working below is a dynamically named range 'ImportedData'. The data in this table will always start on Row 8 but the last row of the table will vary, so the bold/red areas of the formula need to be dynamic.

Can anyone suggest how I might modify this formula to allow for the dynamic table size?

Rich (BB code):
=IF(ROWS(B$19:B19)<=L$19,INDEX('Empower Data'!$D$8:$D$19,SMALL(IF('Empower Data'!$F$8:$F$19=E$17,ROW('Empower Data'!$D$8:$D$19)-ROW('Empower Data'!$D$8)+1),ROWS(B$19:B19))),"")

Any help is appreciated.
Could you just change the red 19s to some larger number that will always be larger than your dynamic range?
 
Upvote 0
Could you maybe replace 'Empower Data'!$D$8:$D$19 with the name of your dynamically named range?
I think this should work - provided a second dynamic named range is also defined.

Assuming the named range (ImportedData) refers to 'Empower Data'!$D$8:$D$xx
then define another named range (ImportedDataF) as:
=OFFSET(ImportedData,0,2)

The the formula becomes
Rich (BB code):
=IF(ROWS(B$19:B19)<=L$19,INDEX(ImportedData,SMALL(IF(ImportedDataF=E$17,ROW(ImportedData)-ROW('Empower Data'!$D$8)+1),ROWS(B$19:B19))),"")
which still needs to be confirmed with Ctrl+Shift+Enter
 
Upvote 0
Great suggestions Peter, thanks for your input. I had already implemented your first suggestion by calculating the largest possible number of rows for my source table 'ImportedData' and that does work.

Your second suggestion intrigued me so I gave it a shot. The 'ImportedData' named range actually refers to a table of data located at 'Empower Data'!$C$8:$O$1132 [largest possible size]. So I gave a range name to my lookup column and each column containing data I wanted to retrieve. It's almost working, except for the item in red below:

Rich (BB code):
{=IF(ROWS(C$19:C19)<=L$19,INDEX(ImportedData_pI,SMALL(IF(ImportedData_ResultID=E$17,ROW(ImportedData_pI)-ROW('Empower Data'!$I$8)+1),ROWS(C$19:C19))),"")}

'Empower Data'!$I$8 refers to the 1st row of the 7th column in my source table 'ImportedData'. This column is now range named 'ImportedData_pI'.
 
Last edited:
Upvote 0
Great suggestions Peter, thanks for your input. I had already implemented your first suggestion by calculating the largest possible number of rows for my source table 'ImportedData' and that does work.

Your second suggestion intrigued me so I gave it a shot. The 'ImportedData' named range actually refers to a table of data located at 'Empower Data'!$C$8:$O$1132 [largest possible size]. So I gave a range name to my lookup column and each column containing data I wanted to retrieve. It's almost working, except for the item in red below:

Rich (BB code):
{=IF(ROWS(C$19:C19)<=L$19,INDEX(ImportedData_pI,SMALL(IF(ImportedData_ResultID=E$17,ROW(ImportedData_pI)-ROW('Empower Data'!$I$8)+1),ROWS(C$19:C19))),"")}

'Empower Data'!$I$8 refers to the 1st row of the 7th column in my source table 'ImportedData'. This column is now range named 'ImportedData_pI'.
I don't see what is wrong with the red bit? If it worked in the earlier version of your formula, I can't see why it would stop working now. How do you know that it is causing a problem?

It should just return the row number of cell $I$8 on the 'Empower Data' sheet. As far as I can see, that is just what you want and that bit will return 8 (unless rows are added/deleted in the region above row 8 of the 'Empower Data' sheet).
 
Upvote 0
I should have chosen my words more carefully. Using 'Empower Data'!$I$8 works correctly. I guess I was thinking I needed to change it's syntax to the new range name for that row,column.


However, since it refers to the first row of my source table, there's no need to do that and I can use it as is. Just don't like using absolute cell references like $I$8 when I can avoid them, in case the make up of the source table is changed at some point.


Thanks very much for your help in getting this sorted out.
 
Last edited:
Upvote 0
I should have chosen my words more carefully. Using 'Empower Data'!$I$8 works correctly. I guess I was thinking I needed to change it's syntax to the new range name for that row,column.


However, since it refers to the first row of my source table, there's no need to do that and I can use it as is. Just don't like using absolute cell references like $I$8 when I can avoid them, in case the make up of the source table is changed at some point.


Thanks very much for your help in getting this sorted out.
Try ImportedData_pI in place of 'Empower Data'!$I$8
 
Upvote 0
That was it! You're a genius Peter. Thanks again for all your help.

EDIT: Oops, spoke/typed too soon. When I copy that formula down it gives me the same result value in every row.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top