Excel VLOOKUP - how it works

With the V-reference, Excel provides a versatile function. The VLook searches the first column of a search area and scrolls to the right to return the value of a cell. The best way to explain how it works is with an example.

Components of the V-reference in Excel

Each function has certain parameters for which you can determine values. The V-Reference function has a total of four such parameters. The structure of the VLOOKUP looks like this = VLOOKUP (search term; search area; column index; match)

1. Keyword: What should the table look for? This can be a fixed value or a cell specification.
2. Search area: In which table should you search for the term? Note that the first column of the selected area must be the column in which the search term is to be searched.
3. Column index: How many columns to the right should the function go to return the value of the cell? Here you enter the column index in the form of 1, 2, 3 etc. The index is counted from the column in which the search term is searched.
4. Match: Should the result found exactly match the search term or only approximately? TRUE = approximately; FALSE = exactly

Example: Price search using the V-reference

Suppose you have an overview of different books in one Excel spreadsheet and their prices in another. Now you would like to add to the overview by looking for the price of a book in the table. This can be set up as follows.

1. First create the table with the overview of the books (see left side of the graphic).
2. Then in a second table the list with the prices (see right side of the graphic).
3. Now select cell F3 in the overview table and enter the following: = VLOOKUP (E3; prices! $ A $ 2: $ B $ 6; 2; FALSE)
4. Now you have already implemented the price search. If you now enter any book ID in field E3, the function provides you with the associated price from the price table.
5. The search area was also provided with the $ symbol. This fixes the area and is no longer dynamic. This means that when copying the formula, the search area is always set to A2 to B6 and is not counted up: A3 to B7, A4 to B8, A5 to B9 etc. To exclude sources of error, always enclose the letters of the search area with this Symbol.

Problems with the Excel-S reference

Although the V-reference is already a very practical Excel function, there are some restrictions.

• The V-link can only return one result. If the search term occurs several times in the search area, the function will only process the first hit. Therefore, make sure that the search term in the search area is clear.
• The V-link does not allow multiple search areas. If the search term is unambiguous, but can appear in one of many tables, you do not have to create several SV references.
• If the function cannot find the search term, it returns an error. You can catch this error and then start a new V-reference. To do this, use the IFERROR function: = IFERROR (VLOOKUP (...); VLOOKUP (...))
• If the first V-link returns an error because it did not find the search term, a second V-link is called up and you can search for the term in another table.

These instructions refer to Excel 2013. You can find out how to mix formulas with text here.