How to use VLOOKUP in Excel
VLOOKUP makes working with Excel considerably easier. You can save yourself the trouble of searching through a large data collection manually and instead use the VLOOKUP formula to render results into another cell instantly. So that you can fully benefit from this Excel function, we’ll explain to you step by step how to do a VLOOKUP in Excel using a simple example as well as offer different ideas for how the function can be used.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organize your data - included in all Microsoft 365 packages!
What is VLOOKUP?¶
VLOOKUP is one of the most useful Excel features and belongs to the lookup and reference functions. Imagine you have a table consisting of several columns containing data that’s been collected, and you need the right result for a certain search query. We’ll use a member directory as an example:
You might know the name of a person but need to find their phone number. With the VLOOKUP formula you don’t need to search the entire spreadsheet for the name. The function searches a column of the directory from top to bottom until it finds the name you’re looking for. Then it returns the corresponding value from the column where the phone numbers are stored.
However, VLOOKUP is limited in that the function only returns one result — namely the first one. If two people in the directory have the same name, the function will only return the number of the first person. In addition, the search criterion must always be in the column furthest to the left. As a result, the table must be structured in such a way that no relevant columns appear to the left of the column where the search is being performed.
The “V” in VLOOKUP stands for vertical since the function searches a column from top to bottom. Similar to VLOOKUP is HLOOKUP, which searches columns horizontally.
What is the syntax for VLOOKUP?¶
To accurately use VLOOKUP in Excel, you need to know how to enter the formula correctly. Every Excel function has a specific syntax that must be entered exactly as it is, otherwise the command will not return the correct result, or an error message will be displayed. The VLOOKUP syntax is as follows:
=VLOOKUP(lookup_value; table_array; col_index_num; range_lookup)
Now, let’s take a closer look at the different parameters:
lookup_value: This refers to the value that you are looking for. You can enter the criterion directly into the formula in words using quotation marks or enter the specific cell that contains the data.
table_array: This is the second argument, or piece of information required, when inserting the VLOOKUP formula. This argument refers to a group of cells that contain the source data and where you will be returning values from.
col_index_num: This refers to the location of the column within the specified table location in numeric value. Note: Column D does not automatically have the index number
4. If you start the data area from column B, then D would have the column index number
range_lookup: Should the results be exact or just approximately equal? With a
FALSEonly the exact match will be shown.
TRUEwill allow for other input and then return the next smallest value. The entry of this parameter is optional. If you leave this part blank, it will take the
1function by default.
Here’s an example of how the VLOOKUP function in Excel looks:
The following error messages can occur when using VLOOKUP in Excel:
#REF!: In the column index, a column was specified that is outside the search range. For example, this can happen if, in a search range that only has three columns, you have entered the number
4in the column index.
#VALUE?: Since VLOOKUP always searches to the right, neither 0 nor a negative number may be entered in the column index. This is because it is not possible to search to the left of the first column.
#NAME?: This error occurs in Excel when a formula is written incorrectly. With VLOOKUP, for example, accidentally typing
"=vlokkup"will result in this type of error message.
So how do you use VLOOKUP exactly? The function is particularly useful when it comes to searching large collections of data and delivering a specific result. As an example, let’s imagine we have a music collection and create a directory in Excel. Every album is given its own entry, although it is not sorted by name but simply by when it was added to the collection.
This guide applies to Microsoft 365 and Excel versions 2021, 2019 and 2016.
Want to store all your Excel documents in one central location so you can share them with other users and access them from any device? HiDrive cloud storage from IONOS offers you these possibilities as well as the highest security standards for data storage!
Find values with VLOOKUP¶
We want to find out which album is in inventory spot number 10. To do this, we can use the VLOOKUP function in Excel:
The cell in which we entered the function should now display “Parallel Lines.”
What if you want to carry out this search more than once? It is possible to merge VLOOKUP into a form that is also connected to the artists and format. For this, we don’t add the lookup value (the inventory number in our example) directly into the formula, but instead reference the cell where we want the result to be displayed:
We put the same function in two more cells but change the column index number to
Combine VLOOKUP with other functions¶
Now, in most cases, the situation is the complete opposite of what we’ve shown in the examples above. You know which album you are looking for and would like to find the inventory number or artist. Using the CHOOSE function, you can get VLOOKUP to do this. This function allows you to essentially change the table location. It does this by displaying VLOOKUP columns in a different place than where they actually are. In the CHOOSE function, you can specify different areas and the order that they should be interpreted in:
For the value parameter, we use the columns from our table and then specify a reverse order in the index. The index tells the function the order in which it should interpret the columns. The index specification is done in curly brackets and the individual numbers are separated by dots.
CHOOSE replaces the table location parameter in our example:
Now the function for an album title gives us the appropriate inventory number. It is also possible to expand the values in
CHOOSE and record the complete data area. In our example, this is not necessary. We can use the inventory number to determine the other values using simple VLOOKUP functions.
There is also a VLOOKUP function in Google’s spreadsheet application. VLOOKUP in Google Sheets works in a similar way to the VLOOKUP function in Excel.
Microsoft 365 for Business with IONOS!
Powerful Exchange email and the latest versions of your favorite Office apps on any device - get started with our free setup assistance.