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)? Let’s review some cases of merging spreadsheets.

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

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:

- Firstly, we should determine the number of row which corresponds with selected loan agreement. This will help to make
**MATCH**function from Lookup and Reference tab. In particular,**MATCH****(A14;K2:K18;0)**give us required result (for loan agreement "" it will be ordinary number*¹5398***13**). The first argument of the function is desired value (loan agreement "" from the cell*¹5398***A14**). The second one is range of cells where we search for agreement (column with-**LoanID****Ê2:Ê18**). The third argument specifies the type of searching (**0**– exact match). - Secondly, using the same approach we have to define ordinary number of column from the second table. Function
**MATCH****(F1;J1:M1;0)**do that and return (e.g.) value “**Collateralname****3**”. - Finally, thirdly we need function which return value in required cell by number of row and column. It’s function
**INDEX**fromTab. The first argument of the function is range of cells (in our case entire spreadsheet -**Lookup and Reference****J2:M18**). Second argument – row number, third argument – column number (we define it via**MATCH**function).

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**).

Slightly modify previous example, but assume that headings of the second table are merged. Hence, **MATCH** function doesn’t 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 don’t forget to fix the range and relevant search fields by dollar sign (**$**) in order to formula work correct and doesn’t show error because of displacement of the ranges.

Using VLOOKUP function for merge spreadsheets

Improve VLOOKUP function (VLOOKUP2)

VLOOKUP for combination by two condition (VLOOKUP3)