COUNT rows to limit number of rows in VLOOKUP

GarC

New Member
Joined
Nov 9, 2011
Messages
15
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
 
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.
 
Upvote 0
@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?
 
Upvote 0
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
 
Upvote 0
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..
 
Upvote 0

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