Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: VLOOKUP

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Clearwater, FL
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

  2. #2
    MrExcel MVP lenze's Avatar
    Join Date
    Feb 2002
    Location
    Helena, MT
    Posts
    13,690
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,655
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •