Results 1 to 7 of 7
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    2 Vlookup Q's (Excel XP)

    Hi all,
    1.
    If I specify the entire sheet as the table array argument, is that going to influence the speed that Vlookup takes to calc?
    2.
    Can Vlookup return a value based on two lookup values. EG. I want the salary of a person based on his name and department. So the lookup value must be equal to both criteria else give #N/A?

    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 Vlookup Q's (Excel XP)

    1) I haven't actually tried it, but I suspect VLOOKUP will take longer if you specify an entire sheet as lookup table and if there is no match.

    2) You could create an extra column that concatenates name and department. Use a VLOOKUP formula that looks for the concatenated value of name and department.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 2 Vlookup Q's (Excel XP)

    1) I have not tested either but I would suspect:

    not much different would be noticed in an approximate match since my understanding is that it searches by halving after each search. So an approximat match of the 65536 rows should be found in only 15 tests. This would be faster than many exact matches used to test with no sluggishness.

    If it is an exact match it could get slow, since it would literally have to search all the cells in the lookup column, though I imagine testing blanks would be much faster than testing cells with any content in them...

    Steve

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2 Vlookup Q's (Excel XP)

    I agree that it probably won't make much difference if you're not looking for an exact match, since the first column is assumed to be sorted.
    I don't know how exact matching is programmed; if it takes the used range of the worksheet into account, speed shouldn't be worse than if you specify the range.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 2 Vlookup Q's (Excel XP)

    Tx for the informed comments, Hans and Steve. I was asked this question the other day, and was not able to answer the person, but your comments are more informed and will give the person something to work with.
    Cheers
    Regards,
    Rudi

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2 Vlookup Q's (Excel XP)

    To add my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> :

    I would NOT suspect that an exact match search would be any slower when there was a match since the search would stop as soon as the match was found, unless empty entries were all at the top of the sheet. In other words, the extra entries would not be searched if there was a match.
    Legare Coleman

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 2 Vlookup Q's (Excel XP)

    Yes, and this adds another TIP for speeding up lookups:
    Put the most used items near the top of the list so XL can find them quicker and quit looking.

    Steve

Posting Permissions

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