Contents
The main purpose of this function is to look up the position of a given element in a set of values. Most often, it is used to search for the ordinal number of a cell in the range where the value we need lies.
The syntax for this function is:
=MORE EXPOSED(What_we are looking for; Where_we are looking for; search_mode)
where
- What_we are looking for is the value to be found
- Where_we are looking for is a one-dimensional range or array (row or column) where the search is performed
- search_mode – how we are looking for: exactly (0), rounded up (-1) or down (1)
Let’s look at some useful options for its application in practice.
Exact search
The classic scenario is to search for an exact text match to find the position of the text or number we need in the list:
Finding the first or last text cell
If you specify an asterisk as the search value, the function will search for the first cell with text and return its position. To find the last text cell, you can change the third argument search_mode from zero to minus 1:
Numbers and empty cells are ignored in this case.
Finding the nearest number or date
If the last argument is set to 1 or -1, then you can implement the search for the nearest smallest or largest number. The table must be sorted in ascending or descending order, respectively. In general, this is somewhat similar to the interval view of the function VPR (VLOOKUP), but there only the search for the nearest smallest is possible, but here there is a choice.
For example, we need to select a generator from the price list for a design power of 47 kW. If the last argument is set to 1 and the table is sorted in ascending order, then we will find the nearest model with the least power (Beast):
If the third argument is -1 and the table is sorted in descending order, then we will find the nearest more powerful model (Bomb):
Linking the MATCH and INDEX functions
Very often the MATCH function is used in conjunction with another extremely useful function – INDEX (INDEX), which is able to extract data from the range by the row-column number, actually implementing the “left VLOOKUP”.
So, in the previous example, you can get not the number, but the name of the generator model very easily:
Well, since Excel internally stores and processes dates as numbers, this approach works 100% with dates as well. For example, we can easily determine at what stage our project is now:
Fundamental limitation of the function MORE EXPOSED is that it can only search in one-dimensional arrays (i.e., a row or a column), but no one forbids using two at once MORE EXPOSEDand invested in INDEXto implement two-dimensional search by row and column at the same time:
- 5 ways to use the INDEX function
- Time-Lapse Viewing with VLOOKUP
- Finding the last value in a row or column
- How to create an auto-stretch range with auto-sizing