Quick merging of big spreadsheets (VLOOKUP 2D)

If you know VLOOKUP function or his horizontal analog HLOOKUP you should remember that this useful function search information only by one parameter, namely in one-dimensional massive by a row or column. But what if we need to find value with matching both parameters (row and columns)? Lets review some cases of merging spreadsheets.

Case 1. Merging of two spreadsheets (using INDEX and MATCH function)

Suppose we need to merge two spreadsheets, which show credit portfolio and portfolio of collateral:

We can use VLOOKUP function to merge every separate column, but if we have spreadsheets with too many rows and columns, it will be horror. But we have solving of this issue, because Excel has two great functions as INDEX and MATCH from Lookup and Reference Tab. This two functions work as VLOOKUP 2D.

So, what we need to quickly merging? Firstly, copy the heat of the second table and paste it near the heat of the first one. The MATCH function will analyze the name of column and return ordinal number of the column, and by number of LoanID will turn required number of row.

In fact, we want to find value of a cell from crossing from a row and column in the table. For clarity, we divide task into three stages:

So, combining all above saying in one formula we get (for cell F14):

=INDEX(J2:M18;MATCH(A14;K2:K18;0);MATCH(F1;J1:M1;0))

Thereby, after copy our formula for the whole range we will attach additional columns. Also you should pay attention at fixing ranges by dollar sigh (you can use F4). For searching for in hat fix only row (F$1), for searching for in Loan agreement fix only column ($A14).

Case 2. Merging of two tables (using VLOOKUP function)

Slightly modify previous example, but assume that headings of the second table are merged. Hence, MATCH function doesnt be work correct.

In such case we can create additional fields above the tables and fill in ordinary numbers of columns manually. Then we can use ordinary VLOOKUP function.

So, the formula for second variant will be as follows:

=VLOOKUP($A3;$J$3:$M$19;E$1;0)

Once again dont forget to fix the range and relevant search fields by dollar sign ($) in order to formula work correct and doesnt show error because of displacement of the ranges.

Related articles:

Using VLOOKUP function for merge spreadsheets
Improve VLOOKUP function (VLOOKUP2)
VLOOKUP for combination by two condition (VLOOKUP3)