You may have heard of this function before, if you haven’t used it, then I suppose the first question is what is VLookup in Excel, closely followed by how do you use it?
What is VLookup?
As I’m sure you can guess, lookup means that given some information, the function will search for it, and give you something back.
V stands for Vertical.
It’s the most popular option, and the information (or table) runs down the page.
There is an alternative called HLookup – I’m sure you’ve guessed that H = Horizontal.
I’m not a fan of HLookup, as it goes across the columns, and it feels like the spreadsheet becomes a lot larger than expected. It’s basically the same – only the direction of the information changes.
What’s it for?
There is really a ‘million’ uses for it. Most popular options include:
- Phone directory
- Price lookup
- Code search
All of the suggestions follow the same type of pattern. Basically you give the function a piece of information, it finds in, and gives you another piece back.
How to use VLookup in Excel Step by Step
Let’s take a look at the function, and then break it down.
=VLookUp(LookUp_Value, Table_Array, Col_Index_Number, [Range_Lookup])
Lookup_Value
Fairly simple option – what information are you giving the function to look for.
E.g. a name
Table_Array
This is a fancy way of saying, the range of cells that have all the information.
Col_Index_Number
The table is going to have at least two columns – one with the information to lookup, and a second with the information to return.
We need to tell the VLookUp which column we want to get back.
It numbers each column starting from 1.
Range_Lookup
This is a sneaky one.
First off, the reason it’s in square brackets [ ], is to let you know that it’s optional.
Now the reason that it’s sneaky is because there’s a pretty good chance that you really do want to include it.
Let’s say that with that list of names, there’s Michelle K and Michelle P.
We don’t want Excel to just look for a Michelle, we want it to find the right one, based on what we’ve typed in.
Range lookup allows us to say ‘find exact matches’, using a True or False option.
Told you – sneaky. 😏
Prep Work
You didn’t think this was going to be quite as easy as a Sum function – did you?
Before you can create your VLookup in Excel, there are things that needs to be setup.
First, you need to have that list of information (Table_array).
All of that info needs to be on a sheet.
Often it’s the same sheet, but you can have the data on a different sheet tab.
I’d keep everything at least in the same file. 😊
Now you’ve got a table of data.
Based on what you’re going to be looking up – it is in a column to the left of the answer?
E.g. If we’re creating a phone directory – is the name to the left of the phone number?
If it isn’t, you’re going to need to rearrange the columns.
While it can be easier to make that lookup info the first column, it doesn’t have to be, but it does have to be the first column in the table array.
Next – what order is your information in?
I hope that you are going to say alphabetical, on the lookup column….
If it isn’t, guess what…
You can see why it is a lot easier to make that lookup value the first column!
Right, that’s the data ready.
Now to make your life easier, and creating the function – write in titles (above or to the left) where you’re going to type in the lookup data and where you want the result to be displayed.
Now that’s all done – let’s create the function.
Writing the Function
Let’s use an example of a simple internal extension directory.
The purpose is to type in a name and see their extension number.
First step is to decide where the function should be written.
You shouldn’t need me to remind you that it’s going to go where the answer is needed.
In this case, it’s where we want the looked up answer to appear, i.e. the extension number.
(Now you see why I said to add the labels in)
In this example it’s cell E7 – under the Ext title.
The Lookup_Value is the name that we’ll type in – that’s cell E4. (Again under the title)
Next is the Table_Array – it’s the range A2 to B10. You’ll notice that I haven’t included the titles, you can include them or not.
Col_Index_Num – I want the extension number returned. Starting from the first column in the table, it’s pretty obvious to see that it’s the second column in (Name = 1, Ext = 2). All that needs to be typed is ‘2’.
Range_Lookup – I specifically got an image that shows the options. If I was to say True, I’ll get back the first result that looks ‘right’ to Excel. What I ALWAYS want to select is False! If I’m typing in an option, I was the right info back – that means I prefer it to be an exact match.
If you haven’t typed anything into the Name cell, then you will get an error back. Just type a name in and you’ll see the answer.
Is it right?
Check another name.
If it works, it’s safe to say that you’ve created your VLookup.
Next Steps
Over to you.
Have a go with your own information.
Have you looked at Excel IF Statement: What do you need to know yet?
Related Posts
- How to choose your favourite online spreadsheet
Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs. - What’s the point of the Excel Information Functions?
What are the information functions in MS Excel? How can you make use of the data they add into your spreadsheet. Read the post to learn more. - How to use Date and Time Functions in Excel
Unsure how to work with dates and times in Excel? This guide will show you how, with a variety of date and time functions explained. - VLookup in Excel: What Do You Need to Know Step by Step
Learn how to use the VLookup function step by step and you’ll find that it’s actually not as difficult as you might think. Check out this guide to some of the basics! - Excel IF Statement: What do you need to know
Unsure of how the IF statement in Excel works? This guide will walk you through all the basics, as well as some more advanced features, so that you can start using this function to its full potential.
vlookup in excel step by step, vlookup in excel, vlookup in excel meaning