Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.




71 posts

Master Geek


# 39811 19-Aug-2009 17:43
Send private message

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 Tongue out
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

Create new topic


71 posts

Master Geek


  # 248695 19-Aug-2009 17:50
Send private message

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


352 posts

Ultimate Geek


  # 249164 20-Aug-2009 19:38
Send private message

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

 
 
 
 




71 posts

Master Geek


  # 249220 20-Aug-2009 22:00
Send private message

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

153 posts

Master Geek
+1 received by user: 1

Trusted

  # 249225 20-Aug-2009 22:25
Send private message

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)
           Geekzone Speedtest  -  Download: 16.28 mbps  Upload: 5.86 Mbps

3295 posts

Uber Geek
+1 received by user: 211

Trusted

  # 249236 20-Aug-2009 22:50
Send private message

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

352 posts

Ultimate Geek


  # 249304 21-Aug-2009 09:06
Send private message

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)

3295 posts

Uber Geek
+1 received by user: 211

Trusted

  # 249332 21-Aug-2009 10:35
Send private message

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

 
 
 
 


352 posts

Ultimate Geek


  # 249356 21-Aug-2009 11:26
Send private message

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!

3295 posts

Uber Geek
+1 received by user: 211

Trusted

  # 249360 21-Aug-2009 11:30
Send private message

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.

Create new topic



Twitter and LinkedIn »



Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:





News »

Huawei's scholarship programme showcases international business to Kiwi undergrads
Posted 22-Jul-2019 17:53


Spark Sport launches across a range of new devices
Posted 22-Jul-2019 13:19


Dunedin selects Telensa to deliver smart street lighting for 15,000 LEDs
Posted 18-Jul-2019 10:21


Sprint announces a connected wallet card with built-in IoT support
Posted 18-Jul-2019 08:36


Educational tool developed at Otago makes international launch
Posted 17-Jul-2019 21:57


Symantec introduces cloud access security solution
Posted 17-Jul-2019 21:48


New Zealand government unveils new digital service to make business easier
Posted 16-Jul-2019 17:35


Scientists unveil image of quantum entanglement
Posted 13-Jul-2019 06:00


Hackers to be challenged at University of Waikato
Posted 12-Jul-2019 21:34


OPPO Reno Z now available in New Zealand
Posted 12-Jul-2019 21:28


Sony introduces WF-1000XM3 wireless headphones with noise cancellation
Posted 8-Jul-2019 16:56


Xero announces new smarter tools, push into the North American market
Posted 19-Jun-2019 17:20


New report by Unisys shows New Zealanders want action by social platform companies and police to monitor social media sites
Posted 19-Jun-2019 17:09


ASB adds Google Pay option to contactless payments
Posted 19-Jun-2019 17:05


New Zealand PC Market declines on the back of high channel inventory, IDC reports
Posted 18-Jun-2019 17:35



Geekzone Live »

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


Support Geekzone »

Our community of supporters help make Geekzone possible. Click the button below to join them.

Support Geezone on PressPatron



Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.