vlookup question

[kbrownk]

I use a lot of vlookups in my formulas. To make the project more efficient, I change my arrays as in the given example:

Change VLOOKUP(I2,G:J,4)
To VLOOKUP(I2,G$2:J$6000,4)

Is this even more computationally efficient? My assumption was that it keeps excel from looking down the entire sheet of rows even though there is no data beyond row 6000.

Thanks,
k

 

[wigi]

Yes, this is a good improvement

 

[Yahya]

i think it better to define dynamic range
for formula Tab --> Define Name
DataRng =OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$G:$G),4)

then use this formula
=VLOOKUP(I2,DataRng,4)

 

[kbrownk]

But doesn't the COUNTA in the OFFSET formula have to search through the entire set of rows in Col G? If so, that's a bigger set of cells then my current formula is using.

Thanks,
k

 

[T. Valko]

I use a lot of vlookups in my formulas. To make the project more efficient, I change my arrays as in the given example:

Change VLOOKUP(I2,G:J,4)
To VLOOKUP(I2,G$2:J$6000,4)

Is this even more computationally efficient? My assumption was that it keeps excel from looking down the entire sheet of rows even though there is no data beyond row 6000.

Thanks,
k

There is no significant difference.

Some (most) functions will only calculate up to the size of the used range. VLOOKUP is one of those functions.

If your last row of data is J6000 then the VLOOKUP function only looks as far as row J6000 even though you might reference the entire columns.

In a quick test...

2 files using identical data.

In both files A1:B1000 was filled with EXACTLY the same unsorted random data.

I entered 20 VLOOKUP formulas in each file. One file used entire columns as range references, A:B, and the other file used the specific range A1:B1000.

File1 = used entire columns as range references
=VLOOKUP(D1,A:B,2,0)

File2 = used the specific range A1:B1000
=VLOOKUP(D1,A1:B1000,2,0)

Here are some comparisons.

File size:
File1 = 57,856 bytes
File2 = 57,344 bytes

Amount of time in seconds that it took to calculate the 20 VLOOKUP formulas. Time is the average of 5 calculations:

File1 = 0.002466 secs
File2 = 0.002424 secs

As you can see there is no significant difference in either comparison. If Excel had to look in the entire columns then the calculation time for File1 should have been much higher than that of File2.

 

[kbrownk]

Perfect, thanks!

 

[T. Valko]

Perfect, thanks!

You're welcome!