Using VLOOKUP function for merge spreadsheets

Task statement

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:

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


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

  1. Enabled precise searching (argument Range Lookup=0) and searched value is not in the Table.
  2. Enabled approximate searching (Range Lookup=1), but the Table, in which we search for values, does not sorted by title increasing.
  3. 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:
  4. 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.