VLOOKUP

The UK's most regular instructor-led training courses.
Training information: excel course · Excel courses · VBA training UK

Resolved · Low Priority · Version 2003

VLOOKUP

Steven has attended:
Excel VBA course

by - delegate Steven [7 posts] (2008 Jul 18 Fri, 16:15) Reply

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?

Version	Date	Location	Places available	Book	Next place rate:
Excel Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2007	2008 Nov 27 Thu	Bloomsbury	3	book now	£240	£245
2003	2008 Nov 28 Fri	Southwark	1	book now	£235	£240
2003	2008 Dec 3 Wed	Bloomsbury	0	FULL
2003	2008 Dec 5 Fri	Bayswater	5	book now	£195	£205
2002/XP	2008 Dec 10 Wed	Tooting	0	FULL
2003	2008 Dec 10 Wed	Bloomsbury	0	FULL
Full Schedule: See all 98 Excel Advanced course dates. Bookings currently available until 24th November 2009.

RE: VLOOKUP

by - trainer Roy bronze contributer [28 posts] (2008 Jul 21 Mon, 16:06) Reply

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

RE: VLOOKUP

by - delegate Steven [7 posts] (2008 Jul 21 Mon, 16:17) Reply

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