An in-depth analysis of the Excel Tiger Balm screening formula 'INDEX-SMALL-IF-ROW'

This article shares how Excel uses formula filtering to complete one-to-many search. It is a classic excel filtering function formula to automatically find formula data.

I always hear experts say that there is a Tiger Balm formula, but what exactly is the Tiger Balm formula, and what can this Excel formula do? Let’s take a look at the following rendering first:

This example is a typical one-to-many search. The search condition is department, and each department corresponds to it in the data source. All are multiple data, and the main purpose of the Tiger Balm formula is to solve some relatively complex problems such as one-to-many search. The formula in the animation above is:

=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20 ),99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0)),"")

Perhaps many friends will be amazed when they see this formula : I can’t understand such a long formula!

Today I will work with you to crack this incomprehensible but very powerful formula routine. Please be patient and read on...

The above formula uses a total of six functions: IFERROR, INDEX , SMALL, IF, ROW and MATCH, of which IFERROR and MATCH are the two auxiliary functions in this example, and the remaining four INDEX-SMALL-IF-ROW are the snake oil formula.

So let’s learn the principle of this core part first:

The formula of cell F4 is:

=INDEX($A$2:$A$21, SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)))

Let’s start with INDEX. The basic function of this function is to give an area, and then return the search result according to the corresponding row and column position. In the above figure, the data area searched by INDEX is the area $A$2:$A$21 where the name is located.

The basic structure of the INDEX function is: INDEX (search area, row and column). If the area is a single row or column, one of the last two parameters can be omitted. In layman's terms, when you take a movie ticket to find a seat, the seats in the entire hall are the area, and the rows and seats are the last two parameters in the formula. In this way, the target location can be found accurately.

In the above example, the area is in one column, so we only need to determine which row each data is in.

Understanding this, our focus should be on the second parameter of INDEX:

SMALL(IF($C$2:$C$21=$F$2 ,ROW($1:$20),99),ROW(A1))

Pay attention to the picture above, the sales department has a total Four records, respectively in rows 5, 8, 9 and 16 of the data area (the data area starts from the second row).

So we hope that when the formula is pulled down, the second parameter of INDEX is the four numbers 5, 8, 9 and 16 (this must be understood).

Attention, we are about to come into contact with the core part of Tiger Balm, please maintain a high degree of concentration...

The basic structure of the SMALL function: SMALL (a set of numbers, The smallest number)

It is recommended that you simulate a simple data to fully understand this function. The method is as follows:

In Enter some numbers in column A. The formula means the smallest number in this column. The result is 2. It’s easy to understand, right? Change the second parameter of the formula to 2 and look at the result:

The penultimate one is 4.

If you want to continue to get the third smallest number, I think everyone can think of what to do, but there will be a problem. We can only modify the second parameter manually and cannot achieve this parameter through drop-down. Change, if you want to be able to pull down, the second parameter needs to use the ROW function, which is modified like this:

ROW function is very simple, what you get is The row number of the parameter. Through this formula, we can sort the data in column A from small to large. Do you think it is interesting?

Back to our Tiger Balm formula, remember what the four numbers 5, 8, 9 and 16 mean. We need to use the SMALL function to get these four numbers in sequence. The idea is to determine whether column C is Consistent with F2, if the line number is the same, if it is different, a number larger than the maximum line number is obtained (the purpose is to prevent it from being found):

To achieve this goal, the intervention of the IF function is needed, so there is:

IF($C$2:$C$21=$F$2,ROW($1:$20),99), use this paragraph as the first parameter of SMALL.

Regarding this IF, it is easier to understand. We can use F9 to see the results of this formula:

Because we There are only 20 pieces of data, so it is enough to use 99 as the third parameter of IF. If the amount of data is relatively large, you can use 9^9, which means 9 raised to the 9th power. Anyway, it is large enough.

If you understand this IF, look at this paragraph againSMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)) Isn’t it so dizzy?

Regarding the SMALL part, you must understand that when the formula is pulled down, we get the numbers we want to get one by one, and then use these numbers as the second parameter of INDEX to get what we ultimately need. result.

The core of Tiger Balm is INDEX, SMALL, IF and ROW. Please be sure to think about it again and again to understand this part of the principle. There is another very important point that needs to be emphasized. The tiger balm formula is an array formula, so we need to hold down Ctrl and shift and press Enter.

As for the initial formula, considering that we need to find the contents of multiple columns, the data area of ​​INDEX uses $A$2:$D$21. When there are multiple columns, you need to provide the column position to find the target value. , so use MATCH(F$3,$A$1:$D$1,0) to determine which column the data is in.

The data of each department is different. We need to pull down the formula a few more lines. At this time, some error values ​​will be generated. Use the IFERROR function in the outermost layer of the formula to mask the error values, so that The query results look very clean.

Today I just used an example of one-to-many search to explain the principle of the Tiger Balm formula. In fact, there are many formulas for Tiger Balm. If you like it, continue to share related examples in the future. Of course, if you finish reading this article It would be even better if you can interpret some complex formulas yourself.

Related learning recommendations: excel tutorial

The above is the detailed content of An in-depth analysis of the Excel Tiger Balm screening formula 'INDEX-SMALL-IF-ROW'. For more information, please follow other related articles on the PHP Chinese website!