VLOOKUP

[cdiffell]

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,

[lenze]

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.

[Chris Davison]

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 ]

[Aladin Akyurek]

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

[Chris Davison]

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 ?

[Aladin Akyurek]

On 2002-04-20 03:54, Chris Davison wrote:

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

Yes.

[Chris Davison]

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