Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## 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)

- 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 blogmy tweetscompany tweetscompany web site)

## 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?

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

News and reviews »

New Air Traffic Management Platform and Resilient Buildings a Milestone for Airways
Posted 6-Dec-2023 05:00

Logitech G Launches New Flagship Console Wireless Gaming Headset Astro A50 X
Posted 5-Dec-2023 21:00

NordVPN Helps Users Protect Themselves From Vulnerable Apps
Posted 5-Dec-2023 14:27

First-of-its-Kind Flight Trials Integrate Uncrewed Aircraft Into Controlled Airspace
Posted 5-Dec-2023 13:59

Prodigi Technology Services Announces Strategic Acquisition of Conex
Posted 4-Dec-2023 09:33

Samsung Announces Galaxy AI
Posted 28-Nov-2023 14:48

Epson Launches EH-LS650 Ultra Short Throw Smart Streaming Laser Projector
Posted 28-Nov-2023 14:38

Fitbit Charge 6 Review
Posted 27-Nov-2023 16:21

Cisco Launches New Research Highlighting Gap in Preparedness for AI
Posted 23-Nov-2023 15:50

Seagate Takes Block Storage System to New Heights Reaching 2.5 PB
Posted 23-Nov-2023 15:45

Seagate Nytro 4350 NVMe SSD Delivers Consistent Application Performance and High QoS to Data Centers
Posted 23-Nov-2023 15:38

Amazon Fire TV Stick 4k Max (2nd Generation) Review
Posted 14-Nov-2023 16:17

Over half of New Zealand adults surveyed concerned about AI shopping scams
Posted 3-Nov-2023 10:42

Super Mario Bros. Wonder Launches on Nintendo Switch
Posted 24-Oct-2023 10:56

Google Releases Nest WiFi Pro in New Zealand
Posted 24-Oct-2023 10:18

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.