TOP
# Join Tables (VLOOKUP)

## What is VLOOKUP

## An example of a problem

## Solution

## Disadvantages of the VLOOKUP function

## Problems when working with the function

## Error suppression

### Related Articles:

The built-in function VLOOKUP is one of the most powerful functions in Excel. It belongs to the three most popular in Excel — after SUM and AVERAGE. Her task is to find the desired value in the data table and display it in the specified cell.

The function syntax includes a lookup value (what you're looking for), a table with one or more columns (where to look), a column index number (from which column to return data), and a lookup type (an additional argument that lets you choose an approximate or exact result).

The VLOOKUP function is commonly used for financial analysis, data management, and working with databases. Using this feature, users can quickly and easily find information in a large data set, which can save time and improve accuracy.

The VLOOKUP function looks for values vertically, that is, among rows (this is indicated by the first letter V - Vertical). In Excel, there is also a similar function HLOOKUP, which has a similar functionality, but makes a horizontal (H - Horizontal) search by columns.

Suppose we have two tables with data - * loan table and collateral table * :

We need to automatically insert collateral into the loan table, based on the agreement number, for further reporting.

In Excel in the standard set of functions in the category ** Lookup and reference ** is a function ** VLOOKUP ** . This function looks for the specified value (in our example, the agreement number) in the leftmost column of the specified table (security table) moving from top to bottom and, finding them, displays the value of the adjacent cell (type of credit security). Schematically, the operation of the function looks like this:

So, let's use the function ** VLOOKUP ** . Select the cell where it will be entered (** E2 **) and open the formula wizard (menu ** Insert - Function **). In the category

Fill them in one by one:

- Lookup Value - the agreement number, which the function will look for in the far left column of the provisioning table. In our case - the agreement number
from the cell*"KF568"***C2**. - Table Array - the table from which we will get the required values is our supply table. We'll use a range for the reference
**$G$2:$H$11**(the range is fixed with a dollar sign**($)**, so that it does not move to the bottom when copying the formula). - Column index number - the serial number (not a letter!) of the column in the provision table, from which we will take the name of the provision. The first column of the provision table has the sequence number 1, so we need to take the type of provision from the second column (number 2).
- Range Lookup - we can enter only two values in this field: TRUE or FALSE:

1) If we enter a value**0**or**FALSE**, then this actually means that only search is allowed__with an exact match__, that is, if the function does not find the agreement number in the provisioning table (if it does not exist), then an error will be displayed**#N/A**(no data).

2) If we enter a value**1**or**TRUE**, this will mean that it is allowed to search not only exact, but also__approximate coincidence__. In most cases, such an approximate substitution can cause a false result when**VLOOKUP**will substitute the values of other agreements. So, you should use approximate search only for numeric values (pre-sorted in ascending order).

It remains to press ** OK ** and copy the entered function across the entire column.

The main disadvantage is that the search for the desired value can only take place in the first column of the given range, and the function can return the desired value only from the columns to the right.

The second disadvantage of VLOOKUP is that the function stops working if you remove or add a column to the lookup table. An inserted or deleted element will change the result of the formula because the function syntax requires that you specify the entire range and the specific column number from which you want to extract data.

Also, the VLOOKUP function has a lookup length limit of 255 characters, otherwise the #VALUE error will be returned!

Function ** VLOOKUP ** returns an error ** (#N/A) ** if:

- Exact search is allowed (argument Range Lookup=0) and the searched value is not in the table
**(Table)**. - Approximate search allowed (Range Lookup=1), but in the table
**(Table)**, in which we search for values, the names are not sorted in ascending order. - The format of the cell from which we take the transaction number (for example,
**C2**in our case) and the cell format of the first column**(G2:G11)**tables are different (for example, numeric and text). This situation is particularly typical when numerical codes are used instead of textual names (account numbers, identification codes, dates, etc.). In this case, you can use functions**VALUE**and**TEXT**to convert data formats. It looks like this:

=VLOOKUP(TEXT(C2);$G$2:$H$11;0) . - The function does not find a value because the code contains spaces and invisible non-printing characters (tape feeds, etc.). In this case, you can use the text function
**(TRIM)**and**(CLEAN)**to remove them:

=VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0) .

To suppress the error message ** (#N/A) ** , when the function cannot find an exact match, you can use the function ** IFERROR ** . This function checks if an error ** (#N/A) ** is the result of work ** VLOOKUP ** , and if so, outputs an empty tape (** "" **) or zero, if not - the result of the work ** VLOOKUP ** .

- 1. What is VBA, basic concepts.
- 2. Debugger (Debugger)
- 3. Working with sheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties (Properties)
- 6.1. Data types (Variables)
- 6.2. Data Types (Continued)
- 7.1. Conditions (Conditions)
- 7.2. Terms (Continued)
- 8.1. Loops (Loops)
- 8.2. Cycles (Continued)
- 9. Procedures and functions
- 10. Dialog windows
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls (Controls)
- 12.3. Control elements (Continued)
- 12.4. Control elements (Exercises)
- 13.1. Arrays (Arrays)
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. SQL language, basic concepts.
- 2. Data selection (SELECT)
- 3. Data sorting (ORDER BY)
- 4. Data filtering (WHERE)
- 5. Use of metacharacters (LIKE)
- 6. Calculation (calculation) fields
- 7. Data processing functions
- 8. Data grouping (GROUP BY)
- 9. Sub-requests
- 10. Combination of tables (INNER JOIN)
- 11. OUTER JOIN
- 12. Combined requests (UNION)
- 13. Adding data (INSERT INTO)
- 14. Creating tables (CREATE TABLE)

- Import of currency rates from the NBU website
- Sum written in English
- Unpivottables (Unpivot)
- Function
Google Translate - Camel Case Function
- Snake Case Function
- Kebab Case Function
- Break Case Function
- Sentence Case Function
- Title Case Function
Fuzzy Lookup Function- Function GETSUBSTR
- Free extension "YouLibreCalc"
- Professional Extension "YLC Utilities"