VLOOKUP

Resolved · Low Priority · Version 2003

Fri 18 Jul 2008, 16:15 Delegate Steven said...

Steven has attended:
Excel VBA Intro Intermediate course

VLOOKUP

How could a VLOOKUP for a very large table that would take up a lot of memory be automated to loop row by row?
Or is there another method of doing this?

Mon 21 Jul 2008, 16:06 Trainer Roy said...

RE: VLOOKUP

Assuming you're using Excel 2003 or earlier, then a worksheet only has 64K rows, and the VLOOKUP function is effectively instantaneous.

Interpreted VBA code is going to be significantly slower. A quick experiment with a For loop takes about 1 second to get the last-but-one record. So I can't see why you'd want to do this in code, rather than by formula.

/Roy

Mon 21 Jul 2008, 16:17 Delegate Steven said...

RE: VLOOKUP

Thanks Roy. my impression was that a lot of formulae in a sheet added considerably to the size of the file, but a loop would keep it "lighter".

Steven

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Change Excel's default font

You can change the default font and font size for all spreadsheets created in Excel by:

1. Going to Tools on the menu bar.
2. Select Options, then General.
3. Next to Standard Font you can change the font and font size.
4. Click OK.

Each new file you start from this point onward should use the font and font size you have selected.

View all Excel hints and tips