VLookup formula in Excel 2003

Forums › IT Pro and developers › VLookup formula in Excel 2003

TechnologyHalfVirgin

71 posts

Master Geek

#39811 19-Aug-2009 17:43

Hi All
First off I am loving this site, I wouldn't knwo what to do sometimes without Geekzone, thank you all so much.
Ok question now. I am doing an Applied Computing course and we need to do a Vlookup formula but I do not understand it and I can not go onto the next question without doing this one first.
I have been to micrsoft help site and its like not sinking in or something, maybe someone could explain it in simple terms

We have to do the table attached in Vlookup.

Employee ID
Name
Location
Title
Salary

1
Kim Quietly
Cheviot
Director
$70,000

2
Chris Massey
Cheviot
Director
$70,000

3
Jim Smith
Cheviot
Salesperson
$35,000

4
Kerry Jones
Cheviot
Salesperson
$35,000

5
Ray Green
El Paso
Salesperson
$32,000

6
Simone Greer
El Paso
Salesperson
$32,000

7
Pablo Gonzalez
El Paso
Salesperson
$32,000

8
Elvira Baker
El Paso
Salesperson
$32,000

Hope someone can explain it to me, as my teacher away untill Monday and I really wanna get ahead.

Thanks
Viv

TechnologyHalfVirgin

71 posts

Master Geek

#248695 19-Aug-2009 17:50

Hi LOL now I feel like a total idiot, I worked it out myself. Sorry to waste time on here

Pluralsight

Learn cloud, mobile, security, data and web technologies with Pluralsight (affiliate link).

rvangelder

352 posts

Ultimate Geek

#249164 20-Aug-2009 19:38

Hi.

I see this question so often, that it's worth posting a reply, if only so someone else can print it out and pin it to their wall.

=VLOOKUP(a, b, c, d)

VLOOKUP parts:
a: The value that identifies the row
b: The table range (excluding the headings)
c: The column number of the value you want returned.
d: Always use FALSE

In the above example, Salary is column number 5 (count them from left to right).
So, if we were looking for the Salary of Employee 6:
a: 6
b: $A$2:$E$9
c: 5
d: FALSE

=VLOOKUP(6, $A$2:$E$9, 5, FALSE)

A few tips about VLOOKUP.
- The value that identifies the row must be the first column of your table, and should contain only unique values.
- The first column does not have to be sorted in any particular order, so long as the fourth parameter is set to FALSE.
- If you have a particularly wide table, it's sometimes hard to count the columns. Here's a quicker way to count the columns. Whenever you select a bunch of cells, the number of selected rows and columns appears in the name box, just left of the formula bar (9R x 13C). Select more than a page of cells and the information moves to a tooltip under your mouse cursor.

While VLOOKUP is handy, I prefer MATCH/INDEX, as they are more powerful lookup.

Rob

TechnologyHalfVirgin

71 posts

Master Geek

#249220 20-Aug-2009 22:00

Wow Thank you for that it will help me with an upcoming assignment and hopefully other ppl too.:-D

_Allan

153 posts

Master Geek

Trusted

#249225 20-Aug-2009 22:25

It should also be noted the column A (or the first column of your range) must be in ascending order, especially if you happen to set the last parameter to true.

_Allan (my blog, my tweets, company tweets, company web site)
Geekzone Speedtest - Download: 16.28 mbps Upload: 5.86 Mbps

bazzer

3438 posts

Uber Geek

Trusted

#249236 20-Aug-2009 22:50

I have to clarify points made by rvangelder and alw. rvangelder mentioned to always set the fourth parameter to FALSE. Obviously, this is not the case otherwise this parameter wouldn't exist. alw says that the key column in the lookup range must be in ascending order especially if you use TRUE for the fourth parameter. This is incorrect, it needs to be in ascending order ONLY when using TRUE for the fourth parameter, if you use FALSE they do not need to be in order.

What is the fourth parameter? It's "range_lookup". The purpose is to provide an exact match or not. FALSE means exact match only, if one is not found results in #N/A. TRUE means exact or next smallest (hence the reason for ordered list), so if an exact match is not found the function returns the previous record from the range. It's useful when the key means "up to and including".

rvangelder

352 posts

Ultimate Geek

#249304 21-Aug-2009 09:06

I was trying to keep things simple because the post was aimed at a beginner, and 99% of the time they'll want an exact match.
I've updated my post to clarify that the first column does not need to be ordered in any way, but I still hold that FALSE should always be used.

As mentioned earlier, I use MATCH/INDEX whereever possible. You can do more with them, including multi-column matches, partial string matching, etc...
eg.
=INDEX($E$2:$E$9, MATCH(6, $A$2:$A$9, 0))

or to find the Salary of the first Salesperson with a name starting with "P"
=INDEX($E$2:$E$9, MATCH(1, ($D$2:$D$9="Salesperson") * (LEFT($B$2:$B$9, 1) = "P"), 0))
(enter as array formula)

bazzer

3438 posts

Uber Geek

Trusted

#249332 21-Aug-2009 10:35

rvangelder: I was trying to keep things simple because the post was aimed at a beginner, and 99% of the time they'll want an exact match.
I've updated my post to clarify that the first column does not need to be ordered in any way, but I still hold that FALSE should always be used.

As mentioned earlier, I use MATCH/INDEX whereever possible. You can do more with them, including multi-column matches, partial string matching, etc...
eg.
=INDEX($E$2:$E$9, MATCH(6, $A$2:$A$9, 0))

or to find the Salary of the first Salesperson with a name starting with "P"
=INDEX($E$2:$E$9, MATCH(1, ($D$2:$D$9="Salesperson") * (LEFT($B$2:$B$9, 1) = "P"), 0))
(enter as array formula)

Not sure why you'd use INDEX/MATCH in that example when VLOOKUP does the same thing and is easier to interpret/maintain, but that's your choice I suppose. Since you advocate VLOOKUP with range_lookup = FALSE, I guess you also always use MATCH with match_type = 0?

How do you lookup the marginal tax rate based on income from this table?

Tax Table

rvangelder

352 posts

Ultimate Geek

#249356 21-Aug-2009 11:26

I advocate Excel beginners always use exact match. While that is not what I wrote, that is what I meant. If a user knows enough to use an approximate match, I'd no longer consider them an Excel beginner :)
Absolutely, I could use MATCH with match_type 1.

MATCH/INDEX are not only as easy to interpret (to a non-beginner), but are easier to maintain than the VLOOKUP equivalent.
In your example, if I inserted a column between columns A and B, the VLOOKUP formula would need a tweak, MATCH/INDEX would not!

bazzer

3438 posts

Uber Geek

Trusted

#249360 21-Aug-2009 11:30

It's easy to come up with specific examples where one is better than the other, but what's the point? They both have their place.