Excel Tips to Impress your Boss #3 VLOOKUP
If you enjoy looking up phone books, which let’s be honest is a weird thing to enjoy, you’re going to love this classic excel function. VLOOKUP! Everyone knows this one by name but surprisingly most don’t know how VLOOKUP works. Let’s take a look at how VLOOKUP works!
The phone book intro was not just comedic genius ( I wish it were ) it was also a good way to think about VLOOKUP. When you want to lookup someone in the phone book you first look through the phone book (table_array) and find the name you want (lookup_value) then you move one column over (col_index_number) where the phone number is (the bold font is here for a reason…this is the part where most people get confused) . VLOOKUP works the exact same way and this is how we put it together
=VLOOKUP ( lookup_value ,table_array, col_index_number , [range_lookup] )
lookup_value – This is the name you want
table_array – This is your TABLE of names and phone numbers
col_index_number – This is the NUMBER of the phone number column in the TABLE.
[range_lookup] – This is optional 0 means it must find an exact match 1 is approximate match (99% of the time you can put this as 0)
That’s all good and well but it’s a clear as mud so let’s look at an example!
In the above example I have taken a few names from my local phone book. The VLOOKUPs on the left show briefly how VLOOKUP works. So the first two entries are our lookup_value (name) and
table_array (names numbers and address) . It’s important to note that the table_array must contain where you are looking for the name and where your number is ( think of it as the table where all the information is contained ).
The third and forth entries in VLOOKUP are just numbers. Note how in the four VLOOKUPs I only change the col_index_number so when it is 1 it returns the value in the first column of the table when its 2 it returns the value in the second column of the table and so on and so forth. However it is important to note that when it is 4 it returns an error since the table is only three columns wide. Finally the forth entry is either 0 or 1 my advice is to go with 0 almost always!
An easy way to remember all this is
=VLOOKUP ( What I use to find ,Table with what I use to find and what I want to find , how many columns to the left is what I want, 0 )
Anyway thanks for reading. I’m off to finish reading my favourite book. I’m on chapter C…so far its just been names and numbers and I can’t get enough of it!
I hope this helped you with your excel experience which your sure to excel in. This is goodbye from me, for now, and as always ‘Remember if you’re stuck in excel …Google is your best friend’
Authored by “Excel Aidan” a self confessed Excel fanatic and near-do-well funny man. See Aidan’s profile here and email email@example.com if you have a suggestion for our next excel topic.
At Barden we invest our resources to bring you the very best insights on all things to do with your professional future. Got a topic you would like us to research? Got an insight you would like us to share with our audience? Drop us a note to firstname.lastname@example.org and we will take it from there!