This guide outlines how to use VLOOKUP in Excel with a step by step guide and examples for VLOOKUP and HLOOKUP.
In Excel you can use the so-called VLOOKUP function to look for specific information. This allows entries in a table to be searched directly. This is not dissimilar to searches we are already doing almost every day on the internet. For example, on Google or Wikipedia.
Let’s imagine that we want to search for the number of inhabitants of Washington.
- We go to Google and enter our search term “Washington” and the information you are looking for “Number of inhabitants”. We come to a Wikipedia article that shows the population figures per state in a table. Like here for example: Wikipedia.
- Now we search for the entry of “Washington” in the table, then go further into the column in which the information stands for Washington, for example in the sixth column. Now we have the result that we have searched for or looked up.
This is exactly what the VLOOKUP in Excel does, so if we can search for something online and read values from tables, we can also safely apply the VLOOKUP. The only problem is that Excel does not really work quite so intuitively and we have to remember what Excel wants from us for the individual functions to work. Also learn How to hide columns in Excel: hide, show, outsource and lock columns
How to use VLOOKUP in Excel: the function and formula
However, knowing how to use VLOOKUP in Excel is much easier with the right steps and tips. The formula is used to find attributes in a spreadsheet. Now let’s look at what the individual formula sections stand for in VLOOKUP. Also see How to use COUNTIF in Excel: guide and examples
- REFERENCE = (search criterion; matrix; column index; [range_reference])
- VLOOKUP = stands for vertical search from top to bottom.
- There is also a horizontal search function: HLOOKUP = stands for the horizontal search from left to right (For example, if you want to look in a timetable, which compartment on Wednesday in the 3rd hour. Then you first search the column headers for the correct day of the week, before jumping down 3 rows.)
- Search criterion = stands for the term we are looking for
- Matrix = stands for the area in which we search for our information.
- Column index = stands for the column in which the information you are looking for is located.
- [Range_Reference] = indicates whether we are looking for exact or inaccurate match (this setting is optional.)
By default, the VLOOKUP is on inaccurate search, i.e. TRUE or 1. However, we often need the exact search, so it is important to enter false or 0 at the end of the VLOOKUP function!
TRUE / 1: inaccurate search
FALSE / 0: exact search
How to use VLOOKUP in Excel: Example using ISBN
This can be explained simply by the example of a bookseller: Each book has a specific ISBN. Now the bookseller has a list in which author, book title and ISBN are entered. With the help of the VLOOKUP function, any ISBN can now be set as a search criterion. This allows the bookseller to output all the details about the book directly on the basis of the ISBN. Also read How to use SUMIF in Excel: step by step guide and examples
A vertical reference, short VLOOKUP, in Excel is structured with the syntax as follows:
=VLOOKUP (value, table, col_index, [range_lookup])
- Search criterion: Write down the input cell here.
- Matrix: Here you enter the range in which Excel should search for the search criterion (i.e. the range of your matrix, including the last required column).
- Column index: Here you enter the column number from which the return value should come.
- [Range_Reference]: Here you have to specify whether the result you are looking for should only match approximately the search term (TRUE) or completely match (FALSE).
- Note: The formats of the matrix and search field must match. So text to text, number to number, etc. On the Internet, you will find synonymous with the VLOOKUP function. This is only the English name of this function.
Use of the VLOOKUP formula
In the following, we want to explain the use of the VLOOKUP formula using an example. To do this, we put ourselves in the above situation of the bookseller. If you want to follow our example, your row and column labels must match ours.
First of all, we have to create a list for Excel VLOOKUP. Let’s write a list with a few books, associated authors and ISBN. To do this, we enter the ISBN in the first column, the title in the second column, and the author in the third column. Price and stock status can also be supplemented. This tabular list is now called a matrix. Then the whole thing looks like this:
The search criterion should always be to the left of the output value.
Now we are building a search field, something like this:
We leave the cell behind “ISBN:” blank. Next, we write the following VLOOKUP formula in the cell below, here for the title (in cell H2):
- The search criterion is in field H1.
- You should search for it in lines A2:E7. The colon at A2:E7 means “to” – i.e. from line A2 to line E7.
- The output attribute is in the second column of this table – hence the 2.
- It should not only be searched approximately, but exactly according to the input value – hence FALSE.
Attention: An error message is displayed here: #NV. This means that the command entered does not yet have a search term. The error is due to the fact that the field H1 is still empty. (Further explanations of VLOOKUP error messages can be found at the end of this article). If you enter an ISBN there, the whole thing looks like this:
In the example of the bookseller, the search field could be supplemented by a few more lines. A VLOOKUP is now entered in each of the output fields so that the correct attribute is output. Thus, the bookseller could find out all the necessary data about the book by entering ISBN as an input.
To simplify this, we have changed the formula.
- The dollar signs fix the values. So if you now copy and paste the formula elsewhere, table A2:E7 will still be used, and cell H1 will continue to be used as an input field. Normally, the input cells would also change when moving the formula.
- If you enter this formula directly in the H2 field, you can drag the field down using the square in the lower-right corner. This copies the formula into the cells below. Then all you have to do is change the specified column, i.e. here from column “2” to the relevant column.
- This finishes the search and output window. Of course, you can adjust the VLOOKUP to any search list. Unfortunately, it is not possible to search for several values at the same time in an Excel VLOOKUP.
How to use VLOOKUP in Excel: examples of use
So that the whole thing does not just stay with dry theory, we have now put together two more examples for you. The above example related to searching for values in a very simple table. The following examples become a little trickier.
Create VLOOKUP in Excel with 2 tables
To do a search with a VLOOKUP, you can also merge data from several tables. In the following, we will show you how to refer to a VLOOKUP to two tables.
To do this, you must format a range of your Excel sheet as a table as described above. Repeat this also for the second range, so that you now have two table ranges in your Excel sheet. The VLOOKUP now used aims to take a search word from the first table and assign a result from Table 2. For example, Table 1 can contain the personal information of a customer and Table 2 the invoice amount.
Assuming that the customer names are in column A of the first table and in column C of the second table, and that the invoice data is in column D of the second table, a possible formula could be:
With this formula, the name in cell A2 in the first table is now used as a search criterion. Table 2, which goes from cells C1 to D10, searches for this criterion. The column index is set to two and only exact results should be output. also read How to find circular references in Excel: step by step
VLOOKUP Holidays – create a calendar
You can also do fewer mathematical things with a VLOOKUP. For example, you can create a calendar in Excel in which you list the holidays. Create the calendar using a template and format it as a table. For example, as a VLOOKUP search, you can then output what is in the calendar on the respective date. These can be own dates or holiday dates.
Problems and error messages
There may be some problem setting up a VLOOKUP. That’s why we have compiled some error messages and application errors and a solution for you here:
As described above, sometimes only “#NV” is displayed in the output field instead of a value. This happens when you refer to a cell in the formula you entered that has no or no suitable content. Alternatively, the formula can also refer to a non-existent value or a missing variable. It is best to go through your entered formula step by step to find the loose end.
Table sorted incorrectly
For a VLOOKUP to work correctly, the column with the search terms must always be on the far left. If this is not the case, falsified results or error messages can occur.
This error can occur if you specify a column number in the column index that does not even appear in the table you format. This can be the case, for example, if the specified column number is too large.
FALSE / TRUE swapped
If you receive results that you would rather not have expected, your entry in [range_reference] may not be correct