# Thread: 2 Vlookup Q's (Excel XP)

1. ## 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

2. ## 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. ## 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. ## 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. ## 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

6. ## 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.

7. ## 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
•