#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 VLOOKUP will pair each State with the right Region |
|
List A has Part Numbers and Part Names List B needs to be cleaned up by using VLOOKUP to retrieve the right data from List A |
|
List of Attendees is 803 people 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 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
- What you want has to be to the right of what you’re looking for
- Relative and Absolute Cell References have to be set
- 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!
thanks, just visited for the first time…am social distancing here at home while my museum has been shuttered indefinitely…i need to amp up my excel skills and you were recc via a blog at BlackBaud community – watching the vid it seemed a bit above my current skill level but was interesting, THANK YOU!!!
Thanks for being in touch! I’m glad you’re updating your Excel skills.
You can find more at my YouTube channel Excel on Fire
and at LinkedIn.