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

 

[Jonmo1]

Not sure I understand. That link appears to suggest the use of OFFSET for creating such a range

Yes, but it's only done 1 time, in the name manager.
If you do
=VLOOKUP(E5,NamedRange,3,0)
And NamedRange used offset..

The offset is only calculated once (in the name manager)
Even if that vlookup formula is dragged down for hundreds of rows (which they usually are..)

But, if you put offset directly in the vlookup, then it's calculated for each formula as it's filled down.

 

[Aladin Akyurek]

@GarC

Is $F$5:$F$90000 in ascending order? If it's not is to ossible to sort $F$5:$H$90000 in ascending order on F and keep it that way?

 

[XOR LX]

Yes, but it's only done 1 time, in the name manager.

Ah, yes, agreed.

Apologies - I thought you were simply alluding to a preference for OFFSET over INDEX in this matter.

Certainly what you suggest is preferable, yes. Though I might add (and I mean no disrespect to the site in question), that INDEX is still the better option here, even if we're talking about its use in a Named Range.

Regards

 

[Jonmo1]

Though I might add (and I mean no disrespect to the site in question), that INDEX is still the better option here

None taken, and I agree with that as well. Index is almost always preferable over Offset if possible.
But in this case A1:INDEX() is somewhat volatile. So I'd take the named range with Offset over that.
But that's just personal preference I guess.

And honestly, that link was just the first thing I found that showed how to do a dynamic named range..
There certainly are many other ways to do them.
It was just the general idea I was suggesting..

 

[XOR LX]

But in this case A1:INDEX() is somewhat volatile. So I'd take the named range with Offset over that.

I meant use that INDEX construction within a Named Range!!

Cheers

 

[Jonmo1]

Ah, ok

Yes agreed