Using VLOOKUP function for merge spreadsheets
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 (Lookup and Reference) find VLOOKUP function and press ОК. There appear window for inputting function arguments:
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 "KF568" from cell 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:
- 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:
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.