Does Vlookup Have Max # of Rows?

jevremovj · Mar 10th, 2002, 10:04 PM

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?

Anne Troy · Mar 10th, 2002, 10:47 PM

No. Actually, I always suggest that people name their lookup range, and select the entire column(s) when they do so.

Aladin Akyurek · Mar 10th, 2002, 10:48 PM

Quote:

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.

Aladin Akyurek · Mar 10th, 2002, 10:53 PM

> 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

Anne Troy · Mar 10th, 2002, 10:57 PM

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.

Dave Hawley · Mar 11th, 2002, 01:43 AM

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 ]

Mar 10th, 2002, 10:47 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,512	No. Actually, I always suggest that people name their lookup range, and select the entire column(s) when they do so. __________________ ~Anne Troy

Mar 10th, 2002, 10:57 PM	#5
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,512	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. __________________ ~Anne Troy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 10th, 2002, 10:04 PM	#1
jevremovj Board Regular Join Date: Mar 2002 Location: Irvine, CA Posts: 87	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?

Mar 10th, 2002, 10:53 PM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	> 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

Mar 11th, 2002, 01:43 AM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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 ]