COUNT rows to limit number of rows in VLOOKUP

[GarC]

I have done this before so know it can be done but I've been banging my head trying to remember the required syntax.

I'm working on a worksheet with a simple VLOOKUP.

=VLOOKUP(E5,$F$5:$H$90000,3,0)

My previous colleague set the last row to 90000 knowing that we would never need 90000 rows. This worksheet usually has fewer than 10000.

I want to insert a COUNTA to see how many rows in column E contain data to limit the scope of the VLOOKUP.

What I have attempted, and seem to remember doing, is something along the lines of

=VLOOKUP(E5,"F5:H"&COUNTA(E5:E90000),3,0)

Alternatively, I have been attempting to create the COUNTA in a separate cell and have the VLOOKUP pull the number of rows from here but couldn't get this to work either

Any help appreciated!

Gar

 

[Scrappy666]

Give this a try:

=VLOOKUP(E5,indirect("F5:H"&COUNTA(E5:E90000)),3,0)

I'm assuming you're counta works alone.

 

[AlanY]

I have done this before so know it can be done but I've been banging my head trying to remember the required syntax.

I'm working on a worksheet with a simple VLOOKUP.

=VLOOKUP(E5,$F$5:$H$90000,3,0)

My previous colleague set the last row to 90000 knowing that we would never need 90000 rows. This worksheet usually has fewer than 10000.

I want to insert a COUNTA to see how many rows in column E contain data to limit the scope of the VLOOKUP.

What I have attempted, and seem to remember doing, is something along the lines of

=VLOOKUP(E5,"F5:H"&COUNTA(E5:E90000),3,0)

Alternatively, I have been attempting to create the COUNTA in a separate cell and have the VLOOKUP pull the number of rows from here but couldn't get this to work either

Any help appreciated!

Gar

may be this,

=VLOOKUP(E5,INDIRECT("F5:H"&COUNTA(E5:E90000)),3,0)

oops, 2 mins too slow!

 

[GarC]

Thank you both. INDIRECT was the part I could not remember. I usually only use it for directing to other worksheets and didn't think of it for this purpose.

Is there a way I can formally thank you so you get points?

 

[bosco_yip]

Or try :

=VLOOKUP(E5,F5:INDEX(H:H,MATCH("zzzz",E:E)),3,0)

Regards

 

[Jonmo1]

I'm glad you have solution, but I'm just offerring opinion here..

Using INDIRECT or F5:INDEX()
Defeats the purpose of trying to limit the rows in the formula (which is to improve performance)

Indirect is a volatile function, and it's believed doing F5:INDEX() is also somewhat volatile.
Volatile functions recalculate every time anything on the sheet changes.
Even if what changed is completely unrelated to any reference made by the formula.

So that becomes just as bad or worse than the original vlookup using 90,000 rows.

There's nothing wrong with using more rows than you need.
It's only 'really' bad when you use the entire column F:H
Though 90,000 for a normally 10,000 row range is a bit excessive.
I usually use a 10% rule.
10% more than a reasonable estimate of the most rows you'll ever use.

So if your range is expected to be no more than 10,000.
Then +10% is 11,000

So try
=VLOOKUP(E5,$F$5:$H$11000,3,0)


A nice alternative is to use Dynamic Named Ranges

 

[bosco_yip]

......Indirect is a volatile function, and it's believed doing F5:INDEX() is also somewhat volatile.
.......

Indirect is a volatile function, while INDEX() is non-volatile.

https://msdn.microsoft.com/en-us/library/aa730921(v=office.12).aspx

 

[Jonmo1]

I'm aware Index isn't officially labled as Volatile.
That's why I said it's "believed to be somewhat volatile"


There have been discussions on the subject.
When it's used like
=A1:INDEX(...)

It does display some volatile behaviors when used that way.

 

[GarC]

Thanks for the additional answers.

I am reluctant to make many changes as I am on a temporary contract covering maternity leave. Although I want to improve things, I feel I have to respect her way of doing things.

 

[XOR LX]

A nice alternative is to use Dynamic Named Ranges

Not sure I understand. That link appears to suggest the use of OFFSET for creating such a range, which seems to contradict your talk on volatility. And OFFSET is certainly a worse culprit in this respect than INDEX.

and it's believed doing F5:INDEX() is also somewhat volatile.

Yes. I believe it is volatile "only at workbook open".

Regards