Does Vlookup Have Max # of Rows?

jevremovj

Board Regular
Joined
Mar 9, 2002
Messages
101
Does anyone know where I can go to find out if there is a row capacity limit when doing Vlookup? I was using a table w/50,000 rows and having some challenges but did not know if it was because of the size of my table?
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
No. Actually, I always suggest that people name their lookup range, and select the entire column(s) when they do so.
 
Upvote 0
On 2002-03-10 21:04, jevremovj wrote:
Does anyone know where I can go to find out if there is a row capacity limit when doing Vlookup? I was using a table w/50,000 rows and having some challenges but did not know if it was because of the size of my table?

I don't think VLOOKUP itself imposes any limits on the hight (or width) of a table.
 
Upvote 0
> Actually, I always suggest that people name their lookup range, and select the entire column(s) when they do so.

Good practice, but not always desirable, especially when the lookup range must dynamically computed. Just curious: Why do you think naming would help here?

Aladin
 
Upvote 0
Not applicable here as in correcting an issue, Aladin. More like a *while I'm at it*. I see many people not using named ranges in their VLOOKUPs and I think it makes them sooo much easier.

:)
 
Upvote 0
Hi jevremovj

The only limitation to the VLOOKUP is the total number of rows on an Excel Worksheet, ie 65536. You might find your problems were not having the optional range_lookup argument set to False. While I certainly agree with tuggy (Dreamboat) on naming ranges I must disagree on using an entire column, unless there is only a couple of formulae referencing the range.

I constantly use dynamic ranges throughout my spreadsheets: http://www.ozgrid.com/Excel/DynamicRanges.htm
as this helps restrict the cells needing to be referenced and thus calculated. For example if you have a data table that has 20,000 rows by 5 Columns 5 wide and you name the Columns A:E you are referencing an extra 227680 cells more than needed. use this in only 10 formulas and you adding a lot of overhead to Excels re-calculations (10*227680). You are better off using a dynamic ranges this way you are only referencing the extra 227680 cells once and not 10 times.

BTW sorting your data can also help a lot.



_________________
Kind Regards
Dave Hawley
OzGrid Business Applications
Microsoft Excel/VBA Training
If it's Excel, then it's us!
This message was edited by Dave Hawley on 2002-03-11 00:45
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

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