VLOOKUP

cdiffell

New Member
Joined
Mar 14, 2002
Messages
46
Hello,

I have a general question about VLOOKUPs. I do a report that pulls some info off of another spreadsheet via vlookup. The report I'm pulling from is not always the same number of rows, so the range I use in my vlookup on my report file is $1:$65536, basically "VLOOKUP-ing" against the entire sheet. The data is usually only around 75-100 lines. I am thinking of changing the range to be $1:$1000 or even less. I want to make sure I always capture all the data on the sheet I'm pulling from, but will I see a significant decrease in calculation time by limiting the range in the VLOOKUP to only 1000 or maybe 500 lines as opposed to the whole sheet?

Basically, it comes down to this: does VLOOKUP take significant time examining all the empty rows in the range, or is it smart enough to sort of ignore them when calculating?

Thanks,
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Yes, VLOOKUP does have to look at each cell, but stops when the 1st match is found. I would suggest you do a search on Mr Excel for dynamic named ranges for use in your VLOOKUP formula.
 
Upvote 0
yeppers, Dynamic range would be good here..

I'll illustrate, purely cos I have nothign better to do tonight havign been stood up yet again at the last minute :

suppose your lookup range is housed in A1 (includes column headings) through to C3000 - so it's 3 columns wide and 3000 deep, but as you say, sometimes it could be 400 rows deep and other times it could be 800 rows deep, or even 9,000 rows deep - you just want to have that nagging question "have I checked my range is big enough?"

I'm assuming one of those columns contains text as mine usually do.

Set up a dynamic named range. That is, a named range which is dynamic.

Insert
Name
Define
title it appropriately : DVLR1 (errrrrrr DynamicVLOOKUPRange1)
refers to : =OFFSET($A$1,0,0,MATCH(REPT("z",255),$B:$B),3)
add
okay

to check it's okay :

edit
goto
reference: =DVLR1
okay

this should now highlight your entire range, all 3 columns and as far down as your text rows go down to

add or delete a few rows and retry this step to make sure it highlights correctly and Bob's your uncle.

As to the formula itself : since the outer sets of brackets do things in order, we need to start with the first expression entirely contained within brackets (parentheses) :

REPT("z",255)

This repeats the character "z" 255 times

I'll refer to this text string from here as "zzzzzzz" for simplicity, just carry it on in your head 250ish times.

Plugging this into the next step :

MATCH("zzzzzzzzz",$B:$B)

this is asking excel to perform the MATCH function, in column B, looking for "zzzzzzzz" and return it's position in the column relative to the starting row. However, MATCH has 3 arguements : lookup value, range, match type.

We have omitted match type. So when this last arguement is ommitted, MATCH assumes it to be "1". When match type is "1", MATCH will find the largest value that is less than or equal to lookup_value.

I have to admit, I struggle a bit with this one, but have come to the conclusion that it returns the largest POSITION IN THE COLUMN that is less than or equal to the position in the column of the lookup value. I do hope I'm right on this otherwise I am totally confused as the help file doesn't make sense otherwise.

SO anyway, on the basis that we never encounter the text string of "zzzzzzzzzzzzz" in our everyday spreadsheets, it will default to finding the largest position in column B of a text string : ie the last row.

Assume our data covers 3,000 rows, this value will be 3,000.

We can therefore substitute this section of our formula with just "3,000" to get the following :

=OFFSET($A$1,0,0,3000,3)

which looks almost understandable !

The OFFSET command performs the following :

Starting at cell A1, moving 0 cells up or down and 0 cells left or right, return the range which is 3000 cells down and 3 across...

so we end up with the range A1 to 3000 rows down from A1 and 3 columns across...... with the pertinant number being 3000 which is totally dependant on the result of our MATCH function looking for the last text cell in column B....



_________________
Hope this helps,
Chris
:)


marker dynamic range
This message was edited by Chris Davison on 2002-04-19 12:40
 
Upvote 0

I'm assuming one of those columns contains text as mine usually do.

Set up a dynamic named range. That is, a named range which is dynamic.


I'd go after a numeric column for defining a dynamic name range, if such a column exists.

Aladin
 
Upvote 0
On 2002-04-19 12:43, Aladin Akyurek wrote:
I'd go after a numeric column for defining a dynamic name range, if such a column exists.

Aladin

....because the search element of the formula would be quicker / more efficient ?
 
Upvote 0
On 2002-04-20 03:54, Chris Davison wrote:
On 2002-04-19 12:43, Aladin Akyurek wrote:
I'd go after a numeric column for defining a dynamic name range, if such a column exists.

Aladin

....because the search element of the formula would be quicker / more efficient ?

Yes.
 
Upvote 0
thanks Aladin.

(Lots of fiscal-year-end payments statistics to sift through soon so I'll be working with lots of files > 25,000 rows)

cheers mate
:)
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top