Why are Excel functions like the worst thing to admit to not knowing? It’s one of those topics where if you already know it – then it’s SO EASY – but if you don’t it’s like just smile and nod and try not to look dumb! Excel Elitists! It’s a thing, I just made it up. But, seriously, It does seem like we all learn Excel tips while on-the-job from our friendly co-workers who show you the way things have ‘always been done’. And, yes, once you show it to us – we totally get it! But until then, I’m just watching you click-clack on your keyboard and hoping I can figure it out when I get back to my desk (says quick prayer to the copy&paste-gods).
WELL, here’s hoping we can introduce an Excel function to you guys in an accessible way and help you understand what it’s all about.
We’re covering the VLOOKUP function in Excel: when to use it, exactly what to put in the arguments (sections between commas) & some meaningless information about previous Bachelorettes ?.
Leggo.
SO, how are we going to use VLOOKUP?
Obvi, we want to use the function to figure out who the first 12 bachelorettes gave their final roses to & spent the rest of their lives with, or at least until the cameras stopped rolling?!
In real life, you’ll typically want to use the VLOOKUP function in Excel when you need to pull in specific information from a larger set of data (or from a data set that is not sorted in the same way you need for an easy copy/paste sitch).
Think in 2120 where there are 100’s of bachelor seasons and 1000’s of contestant. Ain’t nobody got time to look up who Juan Pablo picked, yet we need acompletedata set.
Here are the tables we are workin’ with:
V IMPORTANT: In order for Vlookup to work these two factors MUST be in play:
- Each row of information must have a unique identifier that only pertains to that row. AND, these unique identifiers must exist in both sets of data. (You’re basically going to tell Excel to go find this exact THING in another table. Thing being: unique identifier.
- Once, VLOOKUP finds the match in Table 2, it can only pull information that is to the RIGHT of the unique identifier.
Time to Make VLOOKUP Magic!
Step-by-Step:
1. Click on the cell where you would like the formula to exist(we like to start at the top of our list if there is one)
2. Press “=” to begin an Excel formula, followed by the function: VLOOKUP
3. Press “(“.
**Now you’ll see Excel’s handy-dandy blurb telling you what info it will need in order to work**
Let’s break this up, one argument (the sections between commas) at a time:
- Lookup_Value: It just wants to know “hey what do I need to look up and match in the other set of data”? This is always going to be the unique identifier, which for our example is the Season, perfect. Be sure you are working in the same row that you are writing your formula in.
=VLOOKUP(B4,
- Table_Array: Select the other set of info that you are going to have the formula pulling from. We will select the entire section of unique identifiers (the seasons) and the info we want pulled (the winners).
=VLOOKUP(B4,$G$4:$H$13,
- Col_Index_Num: This is asking which column of data from the Table 2 holds the information that you need pulled back to Table 1. All you have to do is count the columns, starting with the first column selected in your table_array. (Our answer is “2” for the Winner column)
=VLOOKUP(B4,$G$4:$H$13,2,
- [Range_lookup]: Now the formula is asking you to type in TRUE or FALSE. Type FALSE when you want it to find exact matches of the unique identifiers in Table 2. Type TRUE when the unique identifiers don’t need to match perfectly. For our example (and most likely for the rest of our lives) we type in FALSE.
=VLOOKUP(B4,$G$4:$H$13,2,FALSE
4. Press “)” to end the formula.
5. Press Enter to watch it work!
6. Copy and Paste the formula you created to the rest of the cells in your table.
Done & Done! Now, anytime you need to pull specific info from another tab, table or entire worksheet file, try using the VLOOKUP function and you’ll make Chris Harrison proud. ?
