# VLOOKUP for Dummies (or newbies)

VLOOKUP is a powerful and handy excel function. At the same time, it is one of the least understood functions and can often break. Today we’ll try to explain VLOOKUP for newbies in the simplest possible way. We’ll also provide formula examples of the function’s most typical usage.

If you want to skip the text and head straight for a video tutorial, take a look at this Top Ten Formulas and Functions in Excel tutorial that we held.

**Excel VLOOKUP Function – Syntax and Primary uses**

**What is VLOOKUP? **

VLOOKUP is a function that searches for the value you specify. The result returns a matching value from another column. To be more technical, you choose a range, and VLOOKUP looks up the value in the first column. The result it returns is a value from the same row of another column.

The letter “V” in VLOOKUP stands for “vertical.” It is there to differentiate from the function HLOOKUP, where the “H” stands for horizontal. HLOOKUP is different that VLOOKUP only in the way that it looks up a value in a row rather than a column.

All versions of Microsoft Excel have the VLOOKUP function available.

**VLOOKUP Syntax**

The VLOOKUP syntax looks like this:

*VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])*

The VLOOKUP function has four arguments. The first three are required, and the last one is optional.

The easiest way to describe it is this:

- Lookup_value – what you want to lookup
- Table_array – where you want to look for it
- Col_index_num- the range containing the value to return
- [range_lookup]- returns an approximate or exact match

**VLOOKUP Example 1**

In this example, we have a list of fruit in column A, and the amounts of them in column B. Now imagine having hundreds of items in A and B, and you need to find a specific one.

You do that with VLOOKUP. Let’s find the amount of Apples on this list.

What can we see from the formula used?

=VLOOKUP(A8;A2:B5;2;FALSE)

Syntax is this

VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])

The first argument is lookup_value, and it’s Apples in cell A8.

The second argument is the table_array, and we chose everything from A2 to B5.

The third argument is col_index_num, and we entered “w” because that is the second column in the array.

The fourth argument is [range_lookup], and we entered FALSE, which returns an exact match. Because this argument is optional, if you leave it empty, the default value will be TRUE, and that is the approximate value.

The result ended up being 50, which we can see, is correct.

**VLOOKUP Example 2**

In example 2, we’ll try to find the first name of an employee. We know his last name but not his first. In a company with hundreds and thousands of employees, VLOOKUP can be quite useful in finding specific information in this scenario.

Just by looking, we know that we used the VLOOKUP correctly and returned the first name as Olivier.

=VLOOKUP(B3;B2:E7;2;FALSE)

looking at what we did in this formula.

The first argument, lookup_value is B3, we could have manually entered “Fontana” because that was the information we know, but simply clicking on B3 was faster.

The second argument, the table_array, is everything from B2 TO E7.

The third argument, col_index_num, is the number of the column we want to search for. Because we need the first name, we’ll enter number 2. If we wanted to find out the title of the employee, we’d type in number 3 because that is the third column in the table_array.

The fourth argument is [range_lookup], and we entered FALSE, which returns an exact match.

**VLOOKUP Example 3**

For the third example on how to use VLOOKUP, we want to find out what the last name is of the person with ID number 106 is.

=VLOOKUP(A7;A2:E7;2;FALSE)

So what did we do in this VLOOKUP formula?

The first argument is A7 since we are looking for the ID 106.

The second argument, the table_array, is everything from A2 TO E7.

The third argument is the number of the column. The last name is in the second column, so we entered number 2.

And the fourth argument we put FALSE to get an exact match.

We can see that we correctly used the VLOOKUP function and got the correct last name, which is Souse.

**Excel Tips on the VLOOKUP Function**

- The VLOOKUP function cannot look to its left. It always searches the leftmost column of the table_array and returns a value from the column from the right.
- Keep in mind that the VLOOKUP function is not case-insensitive. That means lowercase and uppercase letters are treated as the same.
- Don’t forget the fourth argument, which is TRUE for approximate match and FALSE for an exact match. If you don’t enter anything, the default value is TRUE. The TRUE value will first look for an exact match, and if it can’t find it, it will then look for the next largest value that is less than the lookup value.
- Data in a column needs to be sorted in ascending order when searching for TRUE (approximate match)
- You will get the result #N/A error if the lookup value is not found.

#### Using Wildcards (?,*) in VLOOKUP Formula

Like in most of excel functions, wildcards are available for VLOOKUP as well.

- To match any single character, the question mark “?” is used .
- The asterisk “*” is used to match any sequence of characters.

Imagine you can’t remember the exact text you are looking for, or maybe you are looking for a text string that is part of the cell contents. That’s when you use wildcards.

**VLOOKUP Wildcard Example 1**

In a case where you have a list of hundreds of employees, and you want to find out the particular salary of someone who you can’t remember exactly. You know his name begins with an “ack.”

Let’s search in the database.

=VLOOKUP(I2&”*”;A2:B10;1;FALSE)

What did we do in this VLOOKUP formula?

The first argument we clicked on I2, which is “ack” because we know the last name starts with those three letters. And then we added &”*”.

& – stands for, and the asterisk searches for everything after the ack.

The second argument is the table array, where we entered everything from A2 to B10.

The third argument is the column we want to return. Since we want to return the last name, that is column 1, and we entered 1.

Just like always, we want the exact match, so we entered FALSE as the fourth argument.

Just by looking, we can see that we used the VLOOKUP function correctly and got the correct result.

To return the salary, we can use the same formula and only change the third argument. That is what column to return. Since the salary is in the second column, we enter 2 as the third argument.

=VLOOKUP(I3&”*”;A3:B11;2;FALSE).

If you are looking up data horizontally, instead of vertically, then you need to perform exactly the same as the above, but use HLOOKUP instead of VLOOKUP.

Both VLOOKUP and HLOOKUP work in the same way!

## FAQs

### How to use VLOOKUP in Excel?

Use VLOOKUP in Excel to search for a specified value in a range and return a corresponding value from the range.

The VLOOKUP function syntax is **VLOOKUP (value, table, col_index, [range_lookup])** where **“value”** is the value we are searching for. **“table”** is the data range where we have to search. “**col_index**” is the index of the column that contains the value which needs to be returned. **[range_lookup]** specifies if need an exact match or approx. match

### What is VLOOKUP used for?

Use VLOOKUP when you need to find a particular kind of information corresponding to a variable from a large sheet.

For example, use it when you need to find the phone number of customers when you search for their names, from a sheet that contains all this data in a table.

To see VLOOKUP in action, take a look at the following videos:

Take a look at the VLOOKUP killer released by Microsoft – **XLOOKUP.**

There are so many great resources written on VLOOKUP. One of our favorites is this list of 23 things you should know about VLOOKUP.

To check out the range of Excel courses on Simon Sez IT, go here.

Other Excel classes you might like: