So, we have two tables - *table of loans and table of collateral*:

We need automatically add the collaterals from the table of collateral to the table of loans, orienting at the loan agreement number for further reporting.

In the **Excel** function set, in category** Lookup and reference** is **VLOOKUP **function. This function searches for the specified value (in our example it is number of agreement) in the far left column of the specified table (table of collateral) moving from upstair till downstairs and finding it, displays the content of the neighboring cell (collateral name from the collateral table). Schematically this function work we can show as follow:

Now, use **VLOOKUP **function. Select cell where it will be entered **(E2)** and open function wizard (menu ** Insert - Function**). In category

Fill in them one by one:

**Lookup Value**- number of agreement, which function has to find at the far left column of price list. In our case - number of agreement "" from cell*KF568***C2**.**Table Array**- a table from which we will get required values, that is our table of collaterals. For the reference we use array**$G$2:$H$11**(fix the array by dollar sign**($)**in order to it don't slide down during copy the formula).**Column index number**- index number (not letter!) of column in the collateral table, from which we will take an name of collateral. The first column of our collateral table has number 1, hence we need values from second column (number 2).**Range Lookup**- in this field we can insert only two values:**TRUE**or**FALSE**:- If we insert value
**0**or**FALSE**, it actualy will means that allow searching for only__precise compliance__, that is if the function don't find in the collateral table reqiured number of agreement (if it is absent), then it will display an error #N/A (not available). - If we insert value
**1**or**TRUE**, then it means you allow searching for not precise but__approximate compliance__. In most cases, such approximate substitution may cause bad result, when**VLOOKUP**substitute a number of another agreements. So, approximate searching you should enable only for digital value (which were previously sorted by increasing).

That's all ! Only left to click **ОК** and copy the entered function in the entire column.

**VLOOKUP** function return error **(#N/A)** if:

- Enabled precise searching (argument
**Range Lookup=0**) and searched value is not in the**Table**. - Enabled approximate searching (
**Range Lookup=1**), but the**Table**, in which we search for values, does not sorted by title increasing. - Cell formating, from which we take searched title value (e.i.
**C2**in our case) and cell formating of the first column**(G2:G11)**of the table is a different (e.i. digital and text). This case is especially typical by using numeric codes instead the text titles (number of accounts, IDs, dates, etc.) In this case you can use**VALUE**and**TEXT**functions for converting of data formatting. It looks like this:

**=VLOOKUP(TEXT(B3);Price;FALSE)**. - The function doesn't find the value because in code are gaps or invisible nonprintable sights (line wrapping, etc.). In this case you can use text function
**(TRIM)**and**(CLEAN)**for their deleting:

**=VLOOKUP(TRIM(CLEAN(B3));Price;FALSE)**.

For suppresing an error massage **(#N/A)** when the function cannot find exact match, you can use function **(IFERROR)**.

Function ** (IFERROR)** checks if the error **(#N/A)** is a result of **VLOOKUP** work, and if yes, it output blank string (**""**) or zero, if not - result of **VLOOKUP** work.