Funny version? The Japanese media ranked the men’s singles, Malone was fourth, and Fan Zhendong didn’t make it into the top five. The first was not unexpected.

At present, the Chengdu World Table Tennis Championships, which has attracted the attention of table tennis fans all over the world, has decided the final king. China Guoping, the king of the world table tennis world, won the championship of men’s team and women’s team without accident. This result is also the embodiment of Guoping’s top strength. However, although the champion belongs to Guoping, for some defeated players of Guoping, they have to find something else to show their unconvinced. No, Japanese table tennis, which has been suppressed by national table tennis, came back to China, and the Japanese media released a list of male table tennis players in the World Table Tennis Championships. Funny thing is, in this list, Malone, who won all seven games in the World Table Tennis Championships, was directly ranked fourth. At present, Fan Zhendong, which ranks first in the world, is directly ignored by the Japanese media, and can’t even enter the top five. As for the number one player, I believe you should have guessed it.

Chengdu World Table Tennis Championships, as a major competition this season, almost all the top table tennis players in the world came to Chengdu to participate in this table tennis feast. As the host country and the top table tennis team in the world, Guoping is naturally the favorite of the champion. However, neither the men’s team nor the women’s team let the fans down. In their own home court, Guoping won two championships in a sweeping way. Of course, compared with the women’s team, there are still some waves in the men’s team’s championship.

In the semi-final with the Japanese men’s team, the China men’s team encountered the biggest challenge since the start of the game. Japan’s first brother, Chinese-American Zhang Benzhi, is in very good shape in this World Table Tennis Championships, especially in this Sino-Japanese table tennis semi-final. In order to test Wang Chuqin, the national table tennis coaching staff arranged for Wang Chuqin to serve as the second single against Zhang Benzhihe. Wang Chuqin, like Zhang Benzhihe, is a post-00 player, which can also be regarded as a confrontation between new forces in the future table tennis world. But what I didn’t expect was that Wang Chuqin lost completely in this post-00 showdown.

If Wang Chuqin’s loss is understandable, then the number one player in the world, the key player of China team, the loss of a single Fan Zhendong made the fans feel nervous. In the face of Zhang Benzhihe, Fan Zhendong lost his former swagger and was defeated by Zhang Benzhihe. In the end, although Guoping still relied on its overall strength, it eliminated the Japanese team and advanced to the final. However, this five-game game still made many fans sigh: Fortunately, there is only one Japanese team, Zhang Benzhi and He.

It was Zhang Benzhihe’s performance that completely stunned the Japanese media after the game. After the World Table Tennis Championships, the Japanese media "Tokyo Satellite TV" produced a ranking of men’s table tennis strength in the World Table Tennis Championships. To tell you the truth, this ranking is a bit interesting. He is convinced of the players’ victory in the World Table Tennis Championships as a basis for ranking. In other words, whoever wins more games will be in the top position. That’s right, there is such a funny situation. No one of the main players in table tennis can enter the top three. After all, the seven wins of seven battles, which are as strong as Malone’s, can only be ranked fourth because of too few wins. Fan Zhendong, the number one in the world, can’t even get into the top five in this ranking.

Not surprisingly, in this ranking, the number one player in Japan’s team is naturally Zhang Benzhi and He. He ranked first with 11 wins and 1 loss, and was called the strongest men’s singles player in this World Table Tennis Championships. The second place is Huang Zhen Ting, a Hong Kong player from China who scored 8 wins and 3 losses. The third place is Qiu Dang from Germany, whose record is 8 wins and 4 losses.

To be honest, this ranking is totally unreasonable. After all, because of the strength of national table tennis, all the five main players have strong strength, basically taking turns to play. In this way, Malone’s chances of playing will naturally be less. However, Malone only played seven games, but he won all. Just because he played a few games less than Zhang Benzhi, is he weaker than Zhang Benzhi in strength? It can only be said that this is just the self-satisfaction of the Japanese media, which specially found an angle that is beneficial to Japanese players to make such a ranking. However, it is difficult for them to think of such an angle.

King of Find more than VLOOKUP, do you know for CP functions?

During our daily tabulation, it is often used.Find the work of reference related data.

In fact, this is also one of most small partners in touch.

For example, according to the corresponding results of the employee name, according to the number of item numbers in one item, the corresponding sales, etc.

Today, I will introduce you to a very powerful function combination to achieve this feature.

They are INDEX + MATCH combination.

The specific application scenario is as follows:

01 正 正 查

The correct point here is:Find reference target values ??from left to right.

That is, the lookup value is on the left and the return value is on the right.

As shown below, it is the performance watch of the company employee in the first quarter. We now want to find the corresponding performance according to the name.

Write the following formula in [F2] cell:

=INDEX (C2: C7, Match (E2, B2: B7, 0)))

Formula analysis:

First look up Liu Yan in the [B2: B7] in the [B2: B7], return to the number 2;

This number 2 is then used as the second parameter of the index function, meaning returning to the value of the first parameter in the cell area in the cell area in the cell region.

There are three parameters: Match functions:

Match (lookup value, find area, lookup type)

The MATCH function is specifically used to find a value, where you are in a region or group, and return a number.

This number that is usually returned as a parameter of other functions.

Here, the returned numbers are used as the parameter of INDEX.

If the lookup type of the third parameter is 0, it means an exact lookup.

INDEX usually has three parameters:

Index (Returned area, line number, list number)

Since the first parameter is a single line or a single column, the third parameter can be omitted.

If you don’t illegally, the formula can be written as this:

=INDEX (C2: C7, Match (E2, B2: B7, 0), 1)

02 reverse lookup

The reverse refer to it here is:Find reference target values ??from right to left.

That is, the lookup value is on the right, the return value is on the left.

Also as examples above, for example, we want to find Liu Yan’s employee number.

The formula is as follows:

=INDEX (A2: A7, MATCH (E2, B2: B7, 0)))

Formula analysis:

The formula of this formula is similar to the formula of the forward look, but the first parameter of the index function will change the returned area into the [A2: A7] area.

It can be seen that whether it is looking for a look, it is the same.

Just change the area of ??the first parameter of the index function into the area that needs to return.

03 two-way lookup

The two-way refers herein is:Find the reference target value from the horizontal and vertical two directions.

As shown below: It is a quarter employee payment message, and now I need to find how much Liu Yan’s salary in March is.

The formula is as follows:

=INDEX (B3: D8, Match (G3, A3: A8, 0), Match (H2, B2: D2, 0))

Formula analysis:

First look up Liu Yan in [A3: A8] in the vertical position in [A3: A8], return to the number 2;

Then use the second Match function to find the horizontal position in [B2: D2] in this area, return to the number 3;

Finally, the two numbers are respectively used as the second and third parameters of the INDEX function, meaning returning to the INDEX first parameter [B3: D8] zone "Chapter 3 of the third row in the area of ??this intersection cell, namely: 8606.

Because the two-way lookups here need to involve the position in both directions, we are hereUse two MATCH functions to locate the lines and columns.

04 more conditions

Multi-conditional lookup refers to: Find back to the corresponding target value based on 2 or more conditions, which is often used in our daily work.

As shown below: It is a sales list of all item numbers in all regions. We want to find how much the number of item numbers BH003 in Nanjing is.

Condition 1: Nanjing;

Conditions 2: The item number is BH003.

Write the following formula in the [G2] cell:

=INDEX (C2: C7, Match (E2 & F2, A2: A7 & B2: B7, 0))

Formula analysis:

The basic frame of the formula is the same as above, just first connect two conditions into a new condition form with a "&" text connection;

Then follow the ideas looking forward to the looks to the lookup and return to the sales volume in the [C2: C7] area.

Match function Find the location of "Nanjing BH003" in the newly generated array:

Match ("Nanjing BH003", 0)

Returns the number 3, then this number 3, as the second parameter of the index function, means returning the INDEX first parameter [C2: C7] The value in the cell in the cell area, that is, 72.

?

Because this is an array formula, the formula needs to be ended in the [Ctrl + Shift + Enter] three key. The braces outside the formula will be automatically generated. For small friends who use Office 365, you can end up with a three-key.

Of course, the multi-conditional lookups here can also be applied at both horizontal and vertical two directions.

In addition, no matter how many conditions, just connect multiple conditions to a new condition with a "&" text connector first, then apply the above routine.

05 interval lookup

The interval lookup refers to:If the lookup value is in a certain range, the corresponding target value is returned.

As shown below, the number of bonuses from the employee is required according to the performance interval.

Write the following formula in the [G2] cell:

=INDEX (J2: J5, Match (F2, {0; 41; 81; 121}, 1)))

Formula analysis:

This formula is especially the application of the second and third parameters of the MATCH function.

If the third parameter is 1 or omitted, the MATCH function will look for the maximum of less than or equal to the first parameter, andThe value in the second parameter must be sorted in ascending order.

The maximum value of less than 65 is 41, 41 in this constant array ({0; 41; 81; 121}) is 2, that is, the number 2 is returned to the number 2, and then the index second parameter is finally returned 2000.

Of course, here we can also write this constant number in an auxiliary column, which may be better understood.

As shown below:

06 blurry look

The fuzzy look is to realize the target value by combining with the wildcard ("?" Question mark and "*" asterisk).

As shown below: We want to find a surname Xu to open his head, and how much is the employee number of three characters.

The formula is as follows:

=Index (A2: A7, Match (E2 & "", B2: B7, 0))

Formula analysis:

Here is the key to the Match function supports the usage of wildcard.

Everyone must pay attention to:

"?" Question mark wildcard, represent any single character.

"*" Asterisk wildcard, represents any number of characters.

Here we used two "??" question marks, represent any two characters.

07 summary

Today, we have learned two powerful function combinations (INDEX + MATCH)Six kinds of findings commonly used in work:

? Positive lookup;

? Anti-(reverse) lookup;

? Two-way lookup;

Multi-conditional lookup;

Area lookup;

? Fuzzy lookup.

What is the most look for you usually use? What do you have any questions about VLOOKUP?

Welcome to usmessageOh ~