Join Tables (VLOOKUP)

What is VLOOKUP

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.

An example of a problem

Suppose we have two tables with data - a table of loans and a table of collateral:

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

Solution

In Excel in the standard set of functions in the Lookup and reference category there 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 (collateral table) moving from top to bottom and, finding them, displays the value of the adjacent cell (type of loan collateral). Schematically, the operation of the function looks like this:

So, let's use the VLOOKUP function. Select the cell where it will be entered (E2) and open the formula wizard (menu Insert - Function). In the (Lookup and Reference) category, find the VLOOKUP function and click OK. A window will appear for entering function arguments:

Fill them in one by one:

Lookup Value- the number of the agreement, which the function will look for in the leftmost column of the provisioning table. In our case - the agreement number "KF568" from cell C2.
Table Array- the table from which we will get the required values is our supply table. For the reference, we will use the range $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 the value 0 or FALSE, it actually means that the search is allowed only 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 the error #N/A (no data) will be displayed.

2) If we enter a value of 1 or TRUE, it will mean that not only the exact search is allowed, but also approximate coincidence. In most cases, this approximate substitution can cause false results when VLOOKUP substitutes the values of other transactions. 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.

Disadvantages of the VLOOKUP function

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!

Problems when working with the function

The VLOOKUP function 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 look up the 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 format of the cell of the first column (G2:G11) of the table 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 the VALUE and TEXT functions 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).

Error suppression

To suppress the error message (#N/A) when the function cannot find an exact match, you can use the IFERROR function. This function checks if the error (#N/A) is the result of VLOOKUP, and if so, returns an empty string ("") or null, otherwise the result of VLOOKUP.