Vlookup processor hungry

[Sunline]

Hello all , using 2007 i have this Vlookup function , it also returns 0 if cell is blank .

=IF(DE2="",0,VLOOKUP(DE2,Sheet2!$A$1:$B$18,2,0))

I want to use this function for 10 too 20 cols but using just 3 of thses it takes forever too do a calculation , i have read on here that these are hungry , is there a quicker or better way to look at a cell then obtain the info from range $A$1:$B$18
Thanks .

 

[shg]

For a VLOOKUP, the number of columns doesn't matter, the number of rows does.

For an exact match (last argument 0), Excel has to read the cells linearly; if the lookup table can be sorted in ascending order, you can change the formula to enable Excel to do a binary search, which is exponentially faster.

But if the data is only about 20 rows long as in your example, you'd never see the difference.

 

[Sunline]

Thanks for that , sheet 1 contains 170,000 rows and growing each week by approx 700 rows .
The lookup table (sheet2) is always in acending order .
Ive tried putting more than 3 of these funtions into each separate col but computer jams up .
Seems 3 is the limit for my comp , its not lacking power .
Thanks .

 

[shg]

=IF(DE2="",0,VLOOKUP(DE2,Sheet2!$A$1:$B$18,2,0))

Your example was for 18 rows, not 170,000

Try this:

=IF(DE2="", 0, IF(LOOKUP(DE2, Sheet2!$A$1:$A$10000) = DE2, LOOKUP(DE2, Sheet2!$A$1:$B$10000), 0))

Note that only the first column is used in the first lookup. Adjust row extents to suit.

 

[Sunline]

Thankyou , will give it a try after work tonight .