TOP
# Quick merge of large tables (VLOOKUP2D)

## Description

### 1. Combining tables using INDEX and MATCH

### 2. Combining tables using VLOOKUP

### Related Articles:

Let's consider how to quickly combine two large tables together with columns and rows, that is, make a selection not by one parameter (as the functions VLOOKUP or HLOOKUP), but by two at once (using the functions INDEX and MATCH).

If you are familiar with the function VLOOKUP or its horizontal analogue HLOOKUP, then you should remember that these wonderful functions look for information by only one parameter, that is, in a one-dimensional array - by row or by column. And if we need to select data from a two-dimensional table by the coincidence of two parameters at once - both row and column at the same time? Let's consider several options for combining tables.

Suppose we need to combine two tables that display the loan portfolio and the collateral portfolio:

We can start using the VLOOKUP function to combine each individual column, but if our tables have an extremely large number of rows and columns, this exercise can turn into a real pain. However, there is a fairly simple way out of this situation, since Excel has two excellent functions, INDEX and MATCH from the category References and arrays

Okay, so what do we need to do to quickly join the two tables? Let's start by copying the header of the second table (the one we will join) and paste it next to the header of the first table. According to the name of the cap, the MATCH function will give us the serial number of the column, and according to the agreement number - the serial number of the line we need.

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

- First, we need to determine the line number corresponding to the selected credit agreement. This will help make the MATCH function from the category References and arrays
(Lookup and Reference) . In particular, the formula MATCH(A14; K2:K18; 0) will give us the desired result (for the transaction "№5398" it will be ordinal number 13). The first argument of this function is the searched value (agreement "№5398" from cell A14), the second is the range of cells where we are looking for an agreement (the column with the agreement numbers in the table - K2:K18), the third argument specifies the type of search (0 - exact name match, approximate search is prohibited). - Secondly, in a completely similar way, we must determine the serial number of the column in the table with the content we need. Function MATCH(F1; J1:M1; 0) will do this and issue, for example, the value 3 for Collateralname selected by the user in cell F1.
- And finally, thirdly, we need a function that can output the contents of a cell from a table by row and column number - the INDEX function from the same category References and arrays
(Lookup and Reference) . The first argument of this function is the range of cells (in our case, it is the entire table, i.e. J2:M18), the second is the row number, the third is the column number (and we will determine them using the MATCH functions).

So, combining all of the above into one formula, we get the following formula for cell F14:

That way, by stretching our formula over the entire range, we'll correctly attach the extra fields. It is also necessary to pay attention that when stretching the formula, we need to fix the ranges with a dollar sign ($) (for fixing, you can use the F4 key), for searching by the cap, we fix only the line (F$1 ), for searching by transaction numbers - only column ($A14 ).

Let's slightly modify the previous example and suppose that we have similar tables, but their headers are combined, so the MATCH function will not help us to correctly determine the serial number of the column.

In this case, we can create a technical field above the table, and insert column numbers manually. Then, we can use the already familiar VLOOKUP function.

So, the formula for our second option will be as follows:

Again, do not forget to fix the range and corresponding search fields with a $ sign so that the formula works correctly and does not give an error when shifting the ranges.

- 1. What is VBA, basic concepts.
- 2. Code Debugger
- 3. Working with worksheets (Sheets)
- 4. Working with cells (Ranges)
- 5. Properties
- 6.1. Data types (Variables)
- 6.2. Data types (Continued)
- 7.1. Conditions
- 7.2. Conditions (Continued)
- 8.1. Loops
- 8.2. Loops (Continued)
- 9. Procedures and functions
- 10. Dialog boxes (Dialog boxes)
- 11.1. Workbook events
- 11.2. Worksheet events
- 12.1. Custom forms
- 12.2. Controls
- 12.3. Controls (Continued)
- 12.4. Controls (Exercises)
- 13.1. Arrays
- 13.2. Arrays (Continued)
- 13.3. Arrays (Exercises)
- 14.1. Using Excel functions
- 14.2. Creating a custom function

- 1. What is SQL? SQL essentials.
- 2. Select data from a database (SELECT)
- 3. Sorting the result records (ORDER BY)
- 4. Filter records (WHERE)
- 5. Using wildcards (LIKE)
- 6. Computed (calculated) columns
- 7. Aggregate functions (MIN, MAX, AVG etc.)
- 8. Grouping of the resulting set (GROUP BY)
- 9. Subqueries
- 10. Combining records from two tables (INNER JOIN)
- 11. Outer join (LEFT, RIGHT, FULL JOIN)
- 12. Combining the resulting sets (UNION)
- 13. Insert new records (INSERT INTO)
- 14. Create a new table (CREATE TABLE)