Where do we use the VLOOKUP formula

Office Excel VLOOKUP - simply explained


Using the VLOOKUP formula in Excel is a breeze with the right tricks. The formula is used to find attributes in a list catalog. This can be easily explained using the example of a bookseller: Every book has a specific ISBN. The bookseller now has a list in which the 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 means that the bookseller can have all the details about the book displayed directly using the ISBN.

VLOOKUP: Construction

A vertical reference, or VLOOKUP for short, in Excel is structured as follows:

  • Search criterion: This is where you write the input cell.
  • matrix: Here you enter the area in which Excel should search for the search criterion (i.e. the area of ​​your matrix, including the last column required).
  • Column index: Here you enter the column number from which the return value should come.
  • [Area_reference]: Here you have to indicate whether the searched result should only roughly match the search term (TRUE) or completely match (FALSE).

Note: The formats of the matrix and the search field must match. So text to text, number to number, etc. You can find the term "VLOOKUP" synonymous with the VLOOKUP function on the Internet. This is just the English name of this function.

Using the VLOOKUP formula

Below we want you to use the VLOOKUP formula based on an example bring closer. To do this, we put ourselves in the bookseller's position above. If you want to follow our example, your Row and column names with our to match.

The first thing we need to do is make a list for the Excel VLOOKUP. Let's write one List of a couple of books, associated authors and ISBN. We contribute to this in the first column the ISBN an Indian second column the title and in the third column the author. Price and stock status can also be added. This tabular list is now available as a matrix designated. Then it looks like this:

The search criterion should be always left stand from the output value.

Now we are building Search field on, something like this:

The cell behind "ISBN:"we leave blank. Next we write in the cell below, here for the title (in cell H2), the following VLOOKUP formula:

  • The Search criterion is located in Field H1.
  • It should be on the lines A2: E7 to be searched for. The Colon for A2: E7 means "to" - from line A2 to line E7.
  • The output attribute is in the second column this table - hence the 2.
  • It's not just about, but exactly according to the input value be sought - therefore NOT CORRECT.

Danger: An error message is displayed here: #NV. That means the given command no search term yet Has. The reason for the error is that the field H1 is still empty. More explanations of VLOOKUP error messages can be found here. If you entered an ISBN there, it looks like this:

In the bookseller's example, the search field could still be added a few more lines become. In each of the output fields a VLOOKUP is now entered so that the correct attribute is returned. So the bookseller could use the ISBN entry all the data you need to find out about the book.

To make that easy, we have the Formula still changed. Through the Dollar sign are the Fixed values. So if you copy the formula and paste it elsewhere, nevertheless the table A2: E7 used, and continue to use the Cell H1 as an input field. Normally, the input cells would also change when the formula was moved.
If you use this formula directly in the H2 field you can enter the field using the Square in the lower right corner pull down. So the formula gets into the cells copied below. Then all you need is change the specified column, so here from column "2"on the relevant column.
So that's that Search and output window ready. You can of course adapt the VLOOKUP to any search list. Unfortunately, it is not possible to search for multiple values ​​in an Excel VLOOKUP at the same time.

The VLOOKUP in examples


So that the whole thing doesn't just stick to "dry" theory, we have now put together two more examples for you. The above example referred to looking for values ​​in a very simple table. The following examples get a little trickier.

Create VLOOKUP in Excel with 2 tables
You can also merge data from multiple tables to do a VLOOKUP search. We'll show you how to relate a VLOOKUP to two tables below.

To do this, you have to format an area of ​​your Excel sheet as a table as described above. Repeat this for the second area as well, so that you now have two table areas in your Excel sheet. The VLOOKUP now used aims to take a search term from the first table and assign a result from Table 2. For example, table 1 can contain the personal details of a customer and table 2 the invoice amount.

Assuming that the customer names are in column A of the first table and column C of the second table, and that the billing information is in column D of the second table, one possible formula could be:

With this formula, the name in the first table in cell A2 is now used as a search criterion. This criterion is searched for in table 2, which goes from cells C1 to D10. The column index is set to two, and only exact results are to be output.


VLOOKUP Vacation - Here's How to Use a Calendar
You can also do less math 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. As a VLOOKUP search, you can then, for example, display what is in the calendar on the respective date. These can be your own appointments or vacation appointments.

Problems with the VLOOKUP?

You may encounter some problem setting up a VLOOKUP. That is why we have put together some error messages and application errors together with the solution for you:

# NV display
As described above, sometimes only "#NV" is displayed instead of a value in the output field. This happens when you refer to a cell in the entered formula that has no or no suitable content. Alternatively, the formula can also refer to a non-existent value or a missing variable. The best thing to do is to go through your entered formula step by step to find the loose end.


Table incorrectly sorted
For a VLOOKUP to work correctly, the search term column must always be on the far left. If this is not the case, incorrect results or error messages may occur.

#REF! Error
This error can occur if you specify a column number in the column index that does not appear in the table you have formatted. This can be the case, for example, if the specified column number is too large.

FALSE / TRUE swapped
If you get results that you would not have expected, it is possible that your entry in [Area_Reference] is incorrect. Remember: FALSE for the exact result, TRUE to find a result that is as similar as possible.