Contents
If you are familiar with the function VLOOKUP (VLOOKUP) or its horizontal counterpart HLOOKUP, then we must remember that these wonderful functions look for information on only one parameter, i.e. in a one-dimensional array – by row or by column. And if we need to select data from a two-dimensional table by matching two parameters at once – both by row and column at the same time? Let’s look at some real-life examples of such problems and their solutions.
Example 1. Find value by product and city
Suppose we have the following two-dimensional array of data for cities and goods:
The user enters (or selects from drop-down lists) in the yellow cells the desired product and city. In the green cell, we need to use the formula to find and display the number from the table that corresponds to the selected parameters. In fact, we want to find the value of a cell from the intersection of a certain row and column in a table. For clarity, we divide the problem into three stages.
- First, we need to determine the line number corresponding to the product selected by the user in the yellow cell. This will help make the function MORE EXPOSED (MATCH) from category References and arrays (Lookup and Reference). In particular, the formula MATCH(J2, A2:A10, 0) will give us the desired result (for apple it will be number 6). The first argument to this function is the desired value (Apple from the yellow cell J2), the second is the range of cells where we are looking for a product (the column with products in the table is A2:A10), the third argument specifies the type of search (0 – exact match of the name, approximate search is prohibited).
- Secondly, in a completely similar way, we must determine the ordinal number of the column in the table with the city we need. Function MORE EXPOSE(J3; B1:F1; 0) will do this and issue, for example, for Of Kiev, selected by the user in the yellow cell J3 value 4.
- And finally, thirdly, we need a function that can return the contents of a cell from a table by row and column number – the function INDEX (INDEX) from the same category References and arrays (Lookup and Reference). The first argument of this function is the range of cells (in our case, this is the entire table, i.e. B2:F10), the second is the row number, the third is the column number (and we will determine them using the MATCH functions).
In total, combining all of the above into one formula, we get the solution for the green cell:
=INDEX(B2:F10; MATCH(J2;A2:A10;0); EXPOSE(J3;B1:F1;0))
or in English
=INDEX(B2:F10;MATCH(J2;A2:A10;0);MATCH(J3;B1:F1;0))
Example 2: Approximate XNUMXD Search
Let’s slightly modify the previous example. Suppose we have the following situation:
The idea is that the user must enter in the yellow cells the height and width of the door for, for example, a cabinet that he wants to order from the manufacturing company, and in the gray cell its cost should appear from the table. An important nuance is that if the user enters non-standard size values, then they should be automatically rounded to the nearest available in the table and the cost of manufacturing the door for these rounded standard sizes should appear in the gray cell.
The solution for the gray cell will be almost exactly the same as the previous example:
=INDEX(C7:K16; SEARCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))
=INDEX(C7:K16; MATCH(D3;B7:B16;1); MATCH(G3;C6:K6;1))
The difference is only in the last argument of both functions MORE EXPOSED (MATCH) — Mapping type (here it is minus 1). This is a kind of analogue of the fourth argument of the function VLOOKUP – Range Lookup. Generally speaking, there are three possible values for it:
- 1 – search for the nearest smallest number, i.e. the door dimensions entered by the user would be rounded up to the nearest smallest suitable size from the table. In our case, the height of 500 would be rounded up to 450 and the width of 480 to 300, and the cost of the door would be 135.
- -1 – search for the nearest largest number, i.e. a non-standard height of 500 would be rounded up to 700, and a width of 480 would be rounded up to 600, and the cost would be 462. For business, this is much more interesting! 🙂
- 0 – search for an exact match without any rounding. Used for 100% match of the searched value with one of the values in the table. Naturally, it is used when searching for text parameters (as in the previous example), because rounding is impossible for them.
It is important to note that when using approximate search with rounding, the search range – and therefore the entire table – must be sorted in ascending (for Match Type = 1) or descending (for Match Type = -1) rows and columns. Otherwise, the approximate search will not work correctly!
For an exact search (Match Type = 0), sorting is not needed and does not play any role.
PS Inverse problem
In the comments, they are repeatedly interested in how to do the reverse operation, i.e. to determine the city and product in the first example if we know the value from the table? This will require two small array formulas (do not forget to enter them using the keyboard shortcut Ctrl + Shift + Enter, not the usual Enter):
The principle of their work is as follows:
- we go through all the cells in the range B2:F10 and look for a match with the desired value (13) from cell J4 using the function IF (IF)
- when a match is found, then we determine the row (column) number of the first element in the table in this row (column) using the functions COLUMN (COLUMN) и LINE (ROW)
- we pull out the value of the city or product from the table using the function INDEX (INDEX)
- Using the VLOOKUP function to substitute values
- Dynamic selection from a list using the INDEX and MATCH functions
- Improving the VLOOKUP function (VLOOKUP2)
- VLOOKUP (VLOOKUP) case sensitive
- Reusable VLOOKUP to display all values at once