vlookup

Dave

unread,

May 11, 2002, 6:41:00 PM5/11/02

to

Thanks in adance,
I have a spreadsheet that has a lot of vlookups
looking up data from a 2500 line table is there a better formula or trick
which can significantly speed this up at the moment it takes about 40 50
seconds to recalculate

Dave

Alan Beban

unread,

May 11, 2002, 8:15:14 PM5/11/02

to

Dave wrote:

<<is there a better formula>>

Better than what?

Alan Beban

Harlan Grove

unread,

May 11, 2002, 9:23:28 PM5/11/02

to

"Alan Beban" <be...@postoffice.pacbell.net> wrote...

>Dave wrote:
>
><<is there a better formula>>
>
>Better than what?

...

How helpful! Not even willing to speculate?

>>I have a spreadsheet that has a lot of vlookups
>>looking up data from a 2500 line table is there a better formula or trick
>>which can significantly speed this up at the moment it takes about 40 50
>>seconds to recalculate

If your lookups look like

=VLOOKUP(Val,Tbl,Col)

and the 1st column of Tbl is sorted in ascending order then there's nothing
that'll speed it up significantly. On the other hand, if your lookups look
like

=VLOOKUP(Val,Tbl,Col,False)

you may be able to optimize by sorting the 1st column of Tbl in ascending
order and using two cells rather than one to check for exact matches. For
example,

X99: =MATCH(Val,INDEX(Tbl,0,1))

and

Y99: =IF(INDEX(Tbl,X99,1)=Val,INDEX(Tbl,X99,Col),#N/A)

would be the most efficient lookup work-alike because the MATCH will use
binary search but *will* continue until it finds an exact match *if* one
exists.

The single formula

=IF(VLOOKUP(Val,Tbl,1)=Val,VLOOKUP(Val,Tbl,Col),#N/A)

isn't as efficient because both lookups will be performed when Val has exact
matches in Tbl.

Alan Beban

unread,

May 11, 2002, 10:30:15 PM5/11/02

to

Harlan Grove wrote:

> How helpful! Not even willing to speculate?

Nope.

Alan Beban

...Patrick

unread,

May 12, 2002, 4:50:48 AM5/12/02

to

Buy memory ram (i'm serious)

--
Patrick
(XL2000 WIN ME)
Charleroi (BELGIUM)

"Dave" <da...@davidfield.demon.co.uk> a écrit dans le message de news:
Ox1O8zT#BHA.1692@tkmsftngp05...

Dave

unread,

May 12, 2002, 5:28:39 AM5/12/02

to

Thanks for that,
Here is an example of what I meant
=VLOOKUP($B6,GL_INPUT,4) sorry
Alan if you don't know, don't interfere

Ta

Dave <da...@davidfield.demon.co.uk> wrote in message
news:Ox1O8zT#BHA.1692@tkmsftngp05...

David McRitchie

unread,

May 12, 2002, 8:54:32 AM5/12/02

to

Hi Dave,
A reply yesterday by Harlan Grove for a similar question,
about speeding up VLOOKUP if False argument is used.
http://groups.google.com/groups?as_umsgid=ell2sOV%23BHA.2268@tkmsftngp02

Another question asked today by someone about the
next largest value in VLOOKUP for non exact match
was answered by Harlan in March.
http://groups.google.com/groups?as_umsgid=ell2sOV%23BHA.2268@tkmsftngp02

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"...Patrick" <nospammm...@brutele.be> wrote in message news:esWNZIZ#BHA.2040@tkmsftngp05...