Continuing to develop the idea of VLOOKUP 2D, let’s consider the solution of the search problem not in two, but in three dimensions, when a sheet is added to finding the desired row and column. Consider the following example. Let’s say we have several sheets by city with sales data for products (rows) and stores (columns):
Moreover, in the tables, goods and stores are mixed, i.e. their sequence is different. The number of rows and columns can also be different.
An input form was created on a separate sheet, where the user, using drop-down lists, enters the desired city, product and store in the yellow cells D5, D7 and D9
The contents of the drop-down lists are automatically loaded into the yellow cells from the three blue “smart” tables on the right (how to implement this was described in this article). It is necessary in the green cell D11 to get the amount corresponding to the given product and store, and from the desired sheet.
For the solution, we need only three functions:
- MATCH(lookup_value, array, lookup_type) – searches for a given value in a range (row or column) and gives the ordinal number of the cell where it was found. This function will help us find the ordinal numbers of the row and column in the table where the desired number is located. For example, the formula is:
=MATCH(“Alpha”,A2:G1)
… will calculate the number of the column in the table where the store is located Alpha. The last argument of this function (0) means that we need an exact search.
- INDEX(range; row_num; column_num) – selects a value from a range by row and column number. So, for example, the formula:
=INDEX(B2:G9)
… will give us the contents of the cell in the 3rd row of the 2nd column from the range B2:G9.
- INDIRECT(address_as_text) – turns the address bar in the form of text into a real address. Moreover, the address can easily be glued together from fragments using the concatenation operator &. For example, the formula:
=INDIRECT(A1&”!B3″)
…takes the sheet name from cell A1, pastes an exclamation separator and the address of cell B3 to it. If cell A1 contains the word Moscow, then at the output we will get the link Moscow! B3, i.e. the contents of cell B3 from sheet Moscow.
Now we bring everything together to solve our problem:
The only remaining nuance is that, according to the Excel syntax, if there is a space in the sheet names, then they must be additionally enclosed in apostrophes (single quotes), i.e. reference to cell A1 on the worksheet Nizhny Novgorod, for example, should look like this:
=‘Nizhny Novgorod‘!A1
Thus, for universality, you need to add apostrophes to our formula:
- What is the VLOOKUP function and how to use it to substitute values from one table to another
- How to implement search in two dimensions (VLOOKUP 2D)
- dynamic dropdown list with content