VLOOKUP is #2 on the list of Excel Top 5 Functions

#2 of my Top 5 Excel Functions: VLOOKUP

If VLOOKUP was a song, this would be it, I Like it Like That!

VLOOKUP can turn a 2-day ordeal into a 5-minute task.

Reasons to use VLOOKUP

List of 400 addresses each needing to be assigned one of 9 Regions
Master list of the 9 Regions and associated States

VLOOKUP will pair each State with the right Region

List A has Part Numbers and Part Names
List B has Part numbers and the associated Part Names are obviously wrong

List B needs to be cleaned up by using VLOOKUP to retrieve the right data from List A

List of Attendees is 803 people
List of RSVPs is 744

Who RSVP’d and didn’t attend? Who attended and didn’t RSVP?

Master list of 6000 properties that has more details than you typically need
Smaller list of 200 properties that you work from, and with fewer details.
Now the client wants a detail that’s on the Master list but not on the smaller working list. E.g., if there’s a garage, it is attached?

Use VLOOKUP to instantly retrieve the data from the Master list.

Many of you have tried eyeballing your way through such tasks I know. I’ve talked with many a person who’s contacted me after they’ve tried to eyeball and handle some of these issues (or they gave up), but the VLOOKUP is the secret to handling these in a quicker and cleaner manner.

VLOOKUP SYNTAX

=VLOOKUP(Look for what?, Look where?, What detail do you want?, Do you want exact or something close?)
Official Syntax One way to think about the Syntax Example
Lookup Value What do you want me to look for? 2lb bag of XYZ cake flour
Table Array Where do you want me to look? Corner grocery store
Column Index When/if I find it, what do you want? How many steps to the right should I count? The price
Range Lookup If I don’t find exactly what you want, can I bring you information about something similar?  NO!

Three important points about VLOOKUP

  1. What you want has to be to the right of what you’re looking for
  2. Relative and Absolute Cell References have to be set
  3. Lots of VLOOKUPS in a document can slow down the performance

Here we go!

VLOOKUP in Action

In this example, we have a list of 191 Members. Invitations were sent out and there are 20-ish people who haven’t replied. Who are they?

I hope you like it like that!

Clap your hands if you want some more
Stomp your feet if you like my beat
Clap your hands if you want some more

Folks, let’s keep our data clean and PLEASE stop eyeballing your spreadsheets!

Favorite Functions 3, 4 & 5 coming soon!