Creating a dynamic range using Index

[bearcub]

I'm using the index match formula as my lookup formula:

 INDEX('All SCC Ready to Import'!$A$2:$A$800,MATCH('LocalStateSCC Falcon Positions'!H30,'All SCC Ready to Import'!$E$2:$E$800,0))[/I][/B]

I would like to make this lookup dynamic without having to change the range from A2 :A1000 for example.

I thought I would be able to use the a2:Index(A:A,count(A:A) logic in the formula but where would I stick this inside the index match formula?

Thank you for your help.

michael

 

[cooper645]

Could you not simply insert a table which will naturally encompass all new rows of data?

alternatively you could create dynamic named ranges, which you could then reference which also makes your formula easier to read if you ever need to revisit it later.

 

[bearcub]

You do have some good ideas there. what to you mean by inserting a table?

I haven't thought about using dynamic ranges but you're right that could be an alternative.

However, I would like to know how to nest a dynamic range like this for future reference. One never knows when they come in handy.

I'll keep the dynamic range in mind, that one skipped me completely though I can see where that would come in handy though my spreadsheet is 60 columns wide.

Michael

 

[Aladin Akyurek]

What does column A house - text or numbers? And what does column E house - text or numbers?

 

[bearcub]

In my spreadsheets, they are all text -- first and last names. I have a spreadsheet that is provided by our Governance department (I work for the California Teacher's Association) which tells us who is on the State Council committees. The sheet that this formula sits on is adding a member ID that is on this sheet from the All SCC Ready to Import sheet. It's a workaround that I had to create for our leadership Directory Reports.

Would the approach be different if they were numeric?

Michael

 

[Aladin Akyurek]

In my spreadsheets, they are all text -- first and last names. I have a spreadsheet that is provided by our Governance department (I work for the California Teacher's Association) which tells us who is on the State Council committees. The sheet that this formula sits on is adding a member ID that is on this sheet from the All SCC Ready to Import sheet. It's a workaround that I had to create for our leadership Directory Reports.

Would the approach be different if they were numeric?

Michael

1. Define Arange (adjust for any other more convenient name) in the Name Manager as referring to:

='All SCC Ready to Import'!$A$2:INDEX('All SCC Ready to Import'!$A:$A,MATCH(REPT("z",255),'All SCC Ready to Import'!$E:$E))

2.

Define ​

Erange (adjust for any other more convenient name) in the Name Manager as referring to:

='All SCC Ready to Import'!$E$2:INDEX('All SCC Ready to Import'!$E:$E,MATCH(REPT("z",255),'All SCC Ready to Import'!$E:$E))

​

Now you can invoke:
​

​

=INDEX(Arange,MATCH('LocalStateSCC Falcon Positions'!H30,Erange,0))

​

 

[bearcub]

Thank you Aladdin.

So, the trick is to create a dynamic range first via a named range.

You use the Rept Z, 255 argument to get the last entry in the range (since it can't find all the z's it bounces back to the last one item in the range)? Is this how this work>

Does using the technique affect calculation time if i had ranges going down thousands of rows (I don't in this spreadsheet but I might in the future).

I've seen this technique in a number of Mike Girvin videos where he has quoted you as coming up with this approach (which totally blows me away because I think it is so cool!)

Thank you for the tip.

Michael

 

[Aladin Akyurek]

Thank you Aladdin.

You are welcome.

So, the trick is to create a dynamic range first via a named range.

Yes.

You use the Rept Z, 255 argument to get the last entry in the range (since it can't find all the z's it bounces back to the last one item in the range)? Is this how this work>

Yes.

Does using the technique affect calculation time if i had ranges going down thousands of rows (I don't in this spreadsheet but I might in the future).

No, not much it has to.

I've seen this technique in a number of Mike Girvin videos where he has quoted you as coming up with this approach (which totally blows me away because I think it is so cool!)

Thank you for the tip.

Michael

If needed, you can optimize the look up formula, if you are willing to sort Arange and Erange on Erange in ascending order.

 

[bearcub]

Thank you, I'll add this approach to my template file when I get to work this morning.

Neat stuff, thank you again!

Michael

 

[Aladin Akyurek]

Thank you, I'll add this approach to my template file when I get to work this morning.

Neat stuff, thank you again!

Michael

You are welcome.