Excel VLOOKUP is one of those functions in Excel that keeps on creating demand to be used while sorting or analyzing data in the quickest amount of time.
There are many ways of using this function and due to this flexibility, it’s also prone to generating some errors that need to be dealt with by experts.
This function dates back many years, thus it still hasn’t transcended past the limitations that existed throughout the days, so knowing how to use it properly is of utmost importance.
In this article, I’ll be explaining how the basic VLOOKUP argument works and the ways you can manipulate it to produce accurate outcomes.
I’ve added different scenarios and examples for your proper comprehension, so I hope you’ll come out as a VLOOKUP specialist by the end of this article.
Contents
What Is VLOOKUP In Excel?
When you closely look at the term VLOOKUP, you’ll realize what it means by yourself. V stands for vertical and the look-up implies a search that’ll take place vertically.
Meaning, VLOOKUP is a function in Excel that searches up a particular data, text string, number or cell information from a selected data range.
There are certain adjustments you can make to refine your outcome and merge the VLOOKUP function with other similar functions to generate creative results catering to your necessities.
VLOOKUP Breakdown
For you to be expertly applying VLOOKUP in your worksheet, it’s a must for you to understand the arguments of the VLOOKUP formula. The Syntax looks like this in Excel:
=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])
Here, the lookup_value refers to what data you’ll be searching for in the column. The table_array dictates the source table of that column data.
The column_index_num identifies the column number and tells Excel to draw out data from that specific column. Each of the columns has unique identification numbers that you’ll be using.
[Hint: the first column of table_array is “1”, and it goes on like this]The [range_lookup] portion is basically an optional portion of this formula. By default, it’s set to TRUE which means Excel will show you approximate matches. In case you write down FALSE, Excel’s results will only filter out only if there’s an exact match.
Rules Of VLOOKUP
There are certain rules about VLOOKUP that you have to remember before proceeding further, otherwise you might end up with invalid data that can jeopardize your task.
You Must Include Valid References
Valid references mean you’ll have to properly identify your data range with the table_array portion, the particular column number, and type out the search target precisely.
If any of these criteria aren’t met, you won’t be receiving a proper result and end up with inaccurate data.
It Only Works With Vertical Data
It shouldn’t come to you as a surprise that VLOOKUP only functions properly with vertical data. Meaning, you’ll have to have a data set that’s divided into columns (preferably with column headers).
Excel will find out your target data holding one column as a reference to the one that you’re looking in.
It Functions Using Column Numbers
The concept of column number may seem a little perplexing to you. Have no worry, it’s not complex at all and it’s exactly what it sounds like.
Start from the left-most column of your table_array. It’s column number 1, and gradually progressing toward the right, this number will rise one by one like: 2, 3, 4 etc.
The Look-Up Only Goes One Way
The search in VLOOKUP goes only one way, both horizontally and vertically. It means, Excel will start searching your data from up top and keep climbing down towards the end until it finds out the target data for you.
Again, in the rows, the column you’ll be selecting will become your leftmost column as Excel will only go to the right side of the row and not the left.
To go left in terms of finding out data in the same row, you’ll have to incorporate the HLOOKUP function in Excel.
It Only Returns The First Result It Finds
One of the limitations for the VLOOKUP function is that, if there is more than one data in the range, it’ll only return the first result it stumbles upon.
Meaning, in your given column, suppose there are three recurrences of the same data. Excel will start searching from the top and the very first found data out of three will be delivered while ignoring the rest of the two completely.
It Doesn’t Work With Multiple Finding
Multiply finding here implies that your search direction will go every way and not only top-to-bottom or left-to-right manner.
VLOOKUP’s script isn’t made to perform such a task and you’ll have to combine it with the MATCH function in order to receive such flexible outcomes in your Excel sheet.
How To Use VLOOKUP In Excel?
Suppose, you were thrown a large set of data that includes the age, sex and salary of the employees who are currently working for a company.
You are asked to find out the age of a certain employee from the sheet as fast as you can. What will you do? Yes, you’ll incorporate the VLOOKUP function.
Open that spreadsheet and make room for typing in your formula in an empty cell. I’ve organized it my way so that you can understand better.
- I’ve selected cell H7 and I want Excel to find out Austin Vo’s age there.
- I’ll insert the formula line like this: =VLOOKUP(“Austin Vo”, B4:E15, 3)
- Here, Austin Vo is my target data. Since it’s a text string, I must place it inside “”, otherwise it won’t show up.
- B4:E15 is the data range from which I’ll be looking for the information of desire.
- Column 3 is where all the ages are stored, thus 3 is the last string in the argument. If I was looking for their salary, I’d put in 4 instead of 3.
- Since I’m typing out a name, if I’m uncertain about the spelling, a partial match would suffice. Thus, I haven’t included TRUE or FALSE arguments since it’s TRUE by default.
- Once I press enter, VLOOKUP will nicely find the age of Austin Vo and place it in the H7 cell.
This way, I can find out data using just a one-liner formula and save a bunch of time!
How Does The VLOOKUP Search Work?
The VLOOKUP takes the target data as its initial reference. Then, when you identify the range, Excel will confine its searches only to that area.
Also, another reason for the table_array or the data range identification is that, this is what dictates the column number.
For instance, you may have a data set containing 13 columns, but you know which columns you’ll be performing your searches in.
So in the table array, select the necessary column area, say, 5 columns from the set. The very first of those 5 columns is going to be your column no. 1, and not the first in 13.
Excel takes your reference and goes right to your designated column to find out info of your reference data’s row.
It gets interesting when you pick a target data from column 3 and have the column_index_num set to 1. That means, you’re asking Excel to make a search at the left hand side from your reference point.
VLOOKUP isn’t made to perform this task and will return a #N/A error. It only reinforces the rules that I’ve mentioned earlier in the article.
Different Uses Of VLOOKUP (With Examples)
I’ll be showing you a few different VLOOKUP examples with illustrations in this section for your further clarity on the subject.
Finding Exact Match
Finding an exact match leaves no room for error and falsified data. Suppose, you have a very sensitive data set that contains similar numbers and text strings, it’s ideal in such scenarios to add the FALSE argument at the end.
In my example, I’ll be searching for the 2004 French open champion from a six-year span data set containing the names of winners from all the grand slams.
For this, I’ll be using the absolute references so that any addition to the data and changing of formation doesn’t hamper the outcome. How to do it? Let me show you!
In this task, I’ll pick my reference data from within the data range. Here, 2004 is in the A3 cell, so that’s what I’ll be mentioning in my VLOOKUP command like this:
=VLOOKUP($A$3, $A$1:$E$7, 4, FALSE)
Do you notice the $ signs in-between the cell names? This is because I’m using absolute references as opposed to the A1:E7 pattern of relative reference.
If you use the relative reference pattern, adding any data to the sheet would alter your result based on the newest cell information.
Finding Approximate Match
You may think you won’t need to use the approximate match command in VLOOKUP much. If you think this way, then let me tell you, you’re on the wrong side of this.
Let’s say you have a data set containing names and age of employees. You are asked to find out the employee who’s aged closest to 30.
How do you put this command through? Very simple – using the approximate match to FALSE in the VLOOKUP command. Let me demonstrate.
First thing you’ll need to do is to sort your data by age. To do this, you’ll have to select your data range and apply Filter from the Data tab.
Once done, select the drop-down from the Age column and sort it to “lowest to highest”. If it’s a large set, it’ll still be a lot of data to go through manually.
What you’ll do is, highlight the target range and input this formula:
=VLOOKUP(H6, B5:E15, 3, TRUE)
For my data, it returns the result “Easton Bailey” who is 29 years old, closest to being 30. If I had set my argument to FALSE, then Excel would tell me that there’s no matching result. This is how you can use approximate match in similar contexts.
Finding Wildcard Match
Wildcard matches in VLOOKUP are for incomplete target data. For instance, say you’re working on your result sheet and don’t know the full name of a student except for the first name. Your student’s name is James Walker but typing in only “James” won’t bring you any result.
What to do in times like this? You’ll need to incorporate the &“*” command to your existing VLOOKUP script. Your formula will look something like this:
=VLOOKUP(“James”&”*”,B3:E13,3,FALSE)
This way, Excel will find out the first James it finds on the spreadsheet. If there are duplicate instances of the same first name however, this solution won’t suffice and you’ll have to manually find out the full name of that student.
Another use for the Wildcard match is when you don’t know the full spelling of your student’s name. Let’s say you have a student named “Kibo” whose mark you want to inherit, but you’re not sure if it’s Kibo or Kebo.
In such cases, the “?” will help you out. Place your formula like this in the box:
=VLOOKUP(“K?bo”&”*”, B3:E13, 3, FALSE)
Using it like this will not only find out your student with only the first name, but also with the confusion about their name’s spelling!
What Does The #N/A Error Mean In VLOOKUP?
Whenever you’ll be using the VLOOKUP function, in some instances you might receive the error #N/A. It can one of few things:
- Your target data probably doesn’t exist within the range
- You’ve mis-spelled your target data
- Your formula input has some mistake
- There might be a space in the data that you’re looking for
Whatever the reason may be, you’ll be able to make amends for the error if you check the aforementioned factors.
Fixing The #N/A Errors: IFNA Function
Sometimes the #N/A errors are expected, like you’re looking for column-wide target data in an entire spreadsheet where that data might not exist at all and it is expected.
But a text string in bold format stating #N/A just looks out of place and not professional at all. There are ways you can make it into something constructive that would also mean something.
To do this, you’ll have to add the IFNA function to your existing HLOOKUP formula. Say you’re looking for an employee named Walter White in the list. If he does, it’ll show up. If he doesn’t, generally it was supposed to show #N/A.
But with our IFNA function, the formula should look like something like this:
=IFNA(VLOOKUP(“Walter White”, B5:E15, 4, FALSE), “No, there isn’t”)
If he doesn’t exist in the list, you’ll get a text string outcome that’ll state “No, there isn’t” instead of the error. This looks not only standard, but also leaves evidence about your mastery in Excel.
5 VLOOKUP Tips You Should Be Wary Of
Let me highlight 5 important tips for Excel VLOOKUP from which you’ll learn to avoid receiving errors or falsified data while working with a large data set.
Always Sort Your Data Before Looking For Approximate Match
You need to apply a filter and sort your data in descending order before going for an approximate match. Otherwise, VLOOKUP will arbitrarily find a match that’s less appropriate than the demanded one. This has led me to inaccurate data many times in the past, so you should keep this stored at the back of your mind.
VLOOKUP Can Search Between Two Tables
Although it’s strictly not merging two tables, you can use one table’s cell data as the target to find out its relevant value from another table. One thing you must make sure that the target data string exists on both tables with the same spelling or number, otherwise you’ll receive a #N/A error.
You Can Take Your VLOOKUP Script With Absolute Referencing
If you use the absolute referencing with your VLOOKUP like I showed you before, using the $A$2 format of selecting cell or highlighting table range, then you’ll be able to take that script to your other spreadsheets and apply it there. If the structure is the same, it’ll work perfectly and generate accurate results.
Name Your Tables To Easily Select The “table_array”
You can use your table’s name instead of the table range for the table_array argument. If you don’t know how to name your tables, then follow our in-depth guide to Excel tables where you’ll learn a bunch of things you can achieve with tables in Excel.
Textual & Numeric Value Conflict From The First Column Results In Error
Remember, VLOOKUP takes the first column of your selection array as the reference point. So it’s important that your first column data and the data you’re looking for belong to the same category, like both of them have to be either texts or numbers.
A mixture of two will lead you to errors. One shortcut that you can use to avoid it is to not include the unnecessary columns and work with only the ones that are relevant. This gives you more flexibility over your looking up.
Thoughts
I’ve explored different strategies, sorting ideas and analytic guidelines in this article which have their practical uses in a wide variety of work-fields.
I tried to be thorough in my explanation so that even the beginners can pick up this function and be competent enough to start using it straight away.
If you remember the basic principles of this function and the tips that you should implement or avoid in your flow of work, you should be generating results effectively & immediately.
In due time and first-hand practice, you’ll find yourself content with Excel VLOOKUP and its commands as well as merging with other formulas.
Within just a couple of weeks, it should be a piece of cake for you to the point that you can teach others this fabulous Excel feature!
Related Post: