Why does life have to read history?

Beijing Daily Client | Author Han Sheng

History inspires wisdom. Wisdom is based on the understanding and comprehension level of human nature, things and truth.

Understanding of people

People’s social attributes determine that people have to deal with each other all the time. Therefore, knowing people is an important aspect of being in the world. High-level leaders need to know people better.

Han Xin, the genius commander who helped Emperor Gaozu Liu Bang conquer the world, paid special attention to the analysis and grasp of people. After the hongmen banquet, liu bang was sent to hanzhong, and Han Xin was appointed as the general to command the army under the strong recommendation of Xiao He and others. Liu Bang summoned Han Xin to analyze the odds of fighting against Xiang Yu. Han Xin, who is about to go to war, did not analyze the military issues such as the strength of the two sides, but directly asked Liu Bang to compare the qualities of the top leaders of both sides, and proposed that the top leaders should meet the four indicators of "courage, toughness, benevolence and strength". Therefore, Han Xin pays more attention to the leader’s character accomplishment than the obvious material conditions. Based on this, Han Xin thinks that Liu Bang can be a leader. At that time, when the strength between the enemy and the enemy was wide, this was simply an unexpected inference.

What is the basis of Han Xin? He noticed Xiang Yu’s aristocratic background. Nobles are "expensive" because they are knowledgeable, educated and humble, which is the same at all times and in all countries. This is the spirit of "expensive" and the characteristic of Xiang Yu. Although he is all-powerful on the battlefield, a thousand people are invincible with a roar, but he is gentle and polite to people. Everyone can see the advantages of Xiang Yu, but few people can see the other side of his personality, that is, he is too "cherishing things." Xiang Yu’s inheritance was laid down by himself, which was hard-won and cherished. Therefore, he was reluctant to divide people and was not atmospheric enough. When he won the award, he always put the reward in his hand and weighed it repeatedly. At the end of the award, it was quite different from the expectations of his subordinates and caused dissatisfaction. In the long run, the more men fight, the more boring they will be, and they will leave Germany. This is the fate of Xiang Yu, who can’t unite people closely. Therefore, he will go into decline. On the contrary, Liu Bang started from scratch and made some gains by the efforts of all. These things are not his own savings, so he knows very well that things are done by everyone, and things are divided by everyone. After the division, people are excited and can do even more next time. Therefore, the team will become more and more brave, and talents from all over the world will come in an endless stream. Xiang Yu and Liu Bang have different ways to analyze and deal with problems, so Liu Bang will eventually win. Han Xin’s analysis was incisive and fully proved in the Chu-Han War. Han Xin understood the truth that "the world belongs to the world" from the perspective of human nature, and anyone who wants to take it for himself will be spurned.

In human society, knowing people’s wisdom is the first thing to learn, and knowing people can do things.But this is only one dimension. Can it be invincible only by popularity?

Or Han Xin. When he led an army across half of China in the north and arrived in Shandong today, a strategist named Kuai Tong came to visit and asked him why he was struggling with Xiang Yu. Kuai Tong analyzed the world situation and thought that three armed groups, Liu Bang, Xiang Yu and Han Xin, had been formed at that time. Although Han Xin was a subordinate of Liu Bang, his prominent position depended on the existence of Xiang Yu. Therefore, Kuai Tong advised Han Xin to let Xiang Yu go, so as to promote a tripartite confrontation and ensure his own safety and interests. However, Han Xin explicitly refused, because he read Liu Bang’s kindness of "driving me with his car, clothing me with his clothes, and eating me with his food", and he could not betray Li Yi. Obviously, Han Xin always believed in judging people, so as to defeat all his military opponents, and extended it to the political field, trusting Liu Bang and his "righteousness", showing his political naivety. Most of the problems that military strategists think about are local plane dimensions at that time, while politicians are much more complicated and profound. They are not at the same level, so military strategists are often no match for politicians. As a bystander of political strategists, Kuai Tong saw this clearly, while Han Xin didn’t realize that he was just a soldier, and Liu Bangze was going to be a soldier until Liu Bang deprived him of the King of Qi after the Battle of Gaixia, and even later he was stripped of the packet of the King of Chu and relegated to the vacant position of the imperial court, but it was too late. A general is a strategist who does things, while a general is a politician who commands the overall situation.

From Han Xin, we can see the limitations of being confined to knowing people. People are fickle, the most difficult to grasp, and those who want to be independent of the world just by looking at people will eventually be defeated by others. One-sided people are prone to paranoia, and will fall into the mire of rule by man, making it difficult to achieve great things.

A clear understanding and prediction of the situation

Situation analysis is a comprehensive judgment on physical geography, social sentiment, strength growth and decline and other factors. Su Qin’s analysis of the situation between the six countries and Qin in the Warring States Period is a classic.

After Shang Yang’s political reform, Qin’s military strength increased greatly, and it was militaristic and constantly attacked six countries. The six countries passively defended themselves and adopted the policy of forbearance and submission in succession, trying to ease the attack of Qin. Su Qin, a strategist, felt very strange. If the six countries were United, their territory would account for 4/5 of China, and manpower and material resources would also account for 4/5. Why should they yield to Qin? Concession is to fill Qin’s endless desires with limited land. Although it can be delayed for a while, it will surely die. He persuaded the six countries to unite against Qin one by one, and went to Qi, a big eastern country, to analyze the situation to the king of Qi: Qi has Mount Tai in the south, Langya in the east, Qinghe in the west and Bohai in the north, which is called a country with four blockades; The territory is vast, reaching more than 2,000 miles, with hundreds of thousands of elite soldiers, a mountain of grain, rich people, busy traffic, shoulder to shoulder, curtain-raising, sweating and high spirits. No one can compare the strength of Qi, but it makes people feel ashamed to succumb to Qin.

Su Qin’s situation analysis is based on two points: first, the mountains and rivers win; second, the human and material conditions. The former is congenital, while the latter can be changed. But all changes should be based on respecting nature and conforming to the law, that is, adapting to local conditions. Generally speaking, China or the world has only an abstract meaning. In order to survive and develop properly in China, we must first have a deep understanding of China’s natural conditions, which will form the basis of decision-making, and then consider using nature for social development. By combining nature with people, we can see many mysterious problems clearly. Su Qin was familiar with the geography of China, so he accurately pointed out the weaknesses of Qin Jun’s marching operations and the countermeasures. According to the danger of Guanzhong, the state of Qin is surrounded by mountains and waters, and it is more than self-sufficient. However, if it wants to attack the six countries far away, it must pass through the dangerous mountains, and the roads are rugged and vulnerable to attack. So why are South Korea and Wei unwilling to actively resist? Su Qin analyzed: Korea and Wei are blocking Qin’s eastward advancement, and Qin is determined to win, while Korea and Wei are resisting Qin Jun alone, and even if they win, they will suffer heavy casualties, not to mention defeat. Therefore, Korea and Wei are doing their best to resist Qin’s eastward advancement, in addition to defending the country from extinction, they are still guarding the door for the eastern countries; But if you submit to Qin, you can reduce losses and lead the disaster to the east. Su Qin revealed the key of geopolitics through the relationship between geographical situation and national interests. Therefore, even from their own interests, the eastern countries should resolutely support Korea and Wei, so as to help each other and protect themselves. If they have ulterior motives and try to lead Qin’s disaster to other countries,At most, we can only delay the time of being divided by one. When the truth was made clear, the kings of the six countries saw clearly the gains and losses, so they accepted Su Qin’s suggestion and formed a "vertical and horizontal" alliance to jointly resist Qin, which made Qin dare not covet the East for more than ten years.

Knowing people and analyzing the situation is basically based on the immediate understanding of the local area at that time, which belongs to plane thinking. People and interests change at any time, so do interpersonal or international relations. Therefore, we must understand the principle of interests, but we must never rely solely on interests. It is also inevitable that Su Qin’s "Union" alliance will collapse immediately after his death. In order to make this alliance stronger, we must have a deep understanding of the history, culture and traditions of various countries. Mutual understanding and respect are the basis for establishing an interest coordination mechanism. Many conflicts are caused by mutual ignorance or even judging each other based on self-imagination.With historical thinking, our understanding will change from plane to three-dimensional, and with depth, we will have a more important understanding, thus entering the third level.

A profound understanding of history

After Liu Bang defeated Xiang Yu, he chose Luoyang as the capital of the Western Han Dynasty. There are three main points in his basis: first, Luoyang is among the best in the world; Second, there are Zhongtiao Mountain, Xiaoshan Mountain, Xiong ‘er Mountain and Waifang Mountain in the southwest of Luoyang, with the Yellow River in the north and Chenggao in the east, which can be held; Third, the Eastern Zhou Dynasty took it as its capital and had a historical tradition. Deep down, Liu Bang still has a yearning to be on a par with the Zhou Dynasty. These three points are all reasonable from the perspective of the right place and the right time. However, the truth of a thing must match its nature and scale. If it is a small country, Luoyang has a really good terrain; However, if it is a unified central dynasty, the hinterland of the landscape package around Luoyang is too small to maneuver, which is a "death". The third is even more important. Liu (Lou), Liu Bang’s adviser, analyzed that after the founding of the Zhou Dynasty, the princes and chiefs of the Shang Dynasty and its previous generation were enfeoffed, which showed their tolerance and justice, so they won the support of all parties, and their capital was the center of all nations coming to the DPRK, basically without fortification; However, you have been fighting bloody battles for several years, and the society is full of sorrow and hatred. Therefore, the capital needs heavy defense, which is incomparable with the Zhou Dynasty. You can’t choose Luoyang with incomplete terrain as the capital. Liu Bang understood the truth and immediately made his capital in Chang ‘an, Guanzhong.

Since entering the imperial country, whether the capital is properly chosen or not is related to the rise and fall of the dynasty or even the success or failure. Xiang Yu established the Kingdom of Western Chu, with its capital in Pengcheng. This is a land of four wars, and from the perspective of being the capital, it is actually a lower domain. However, Xiang Yu ignored the history and the situation of mountains and rivers, and even cooked and killed the advisers who advised him to make his capital in Guanzhong, and went his own way. As a result, the regime of Xi Chu did not have peace for a year, and he committed suicide in Wujiang River just a few years later. Vision and pattern determine the scale and success or failure of the cause.

It is extremely important to learn to look at problems historically. History teaches us to look at problems completely and systematically and avoid fragmented thinking.When many people think about a problem, they only extract some fragments closely related to it, but don’t understand it from the whole process. People who lack historical thinking only see trees instead of forests, but they can’t see through the essence when they see problems. Based on the fragmented thinking of a specific time and space, the opinions and conclusions are often tempting to fall into the trap. Sadly, people who fail are often convinced of their one-sided views. Sima Qian lamented Xiang Yu’s death, which is a great historian’s regret for paranoid politicians.

Zhang Liang became an outstanding strategist in the early Han Dynasty because he not only knew how to judge people and understand the geographical situation of mountains and rivers, but also knew how to grasp its context from the whole process of historical development, see through the phenomenon, directly attack the essence, and analyze it from three dimensions: interest relationship, geopolitics and historical tradition, thus surpassing Su Qin and Han Xin and assisting Liu Bang in establishing a stable Han Dynasty.

Studying history is the advanced stage of life, and we go in step by step. Our life and our understanding of the whole society are gradually improving, so we must read history in life.

(The author is a professor of history at Fudan University)

Nature’s subversive discovery: Everyone has a "sixth sense"? Will future computers run on human brain cells? | This week is worth reading

Biology and medicine

The subversive discovery in Nature is that everyone may have a "sixth sense", but they just don’t realize it.

@ Academic Jingwei Nature

Why are fingerprints unique? Cell reveals the mystery of fingerprint formation

@ Academic Jingwei Cell

Common sugar substitutes in zero-sugar drinks may increase the risk of heart disease and thrombosis.

@ Science Circle Nature

Cell accidentally found that there are these similarities in the brains of fighters and onlookers.

@ Academic Jingwei Cell

Computer artificial intelligence

New posture of scientific research: let GPT-3 help you.

@ qubit arXiv

When organ-like intelligence shines into human reality: will the future computer run on human brain cells?

@ biological exploration Frontiers in Science

A snapshot can restore a video! AAAI 2023 proposes a new snapshot compression imaging algorithm.

@ qubit AAAI 2023

Materials and chemistry

Wang Qing/Huang Xingyi Nature: Energy storage materials, a major breakthrough!

@ nanohuman Nature

The key role of the latest Nature: H in superconductivity in Electronic Science and Technology University of China.

@ nanohuman Nature

Using metal atoms to construct the skeleton of polymer, it is the longest metal polymer so far.

@ Global Science Angewandte Chemie International Edition

Astronomy and physics

The asteroid "Dragon Palace" contains about 20,000 kinds of organic molecules.

@ Global Science Science

Study on local structure in disordered materials by neutron scattering

@ Keai KeAiNuclear Analysis

The most powerful explosion in the universe

@ 京京京京京京京京 arXiv

Cover image source: Unsplash

The mystery in space, human beings may never be able to open artificial intelligence or move towards the future.

Is it true that "birds fly away with husband and wife, and people are virtuous and have high character"? People will have different moods in different environments, do different things, and their thoughts will change. It is said that many astronauts, and foreign astronauts, look back in space and see that the earth is so small. In an instant, I also feel that human beings are small, even forgetting the nationality, and I hope that the "global village" will unite. Challenging the mysterious space together, although few astronauts talked about it, according to the network, most astronauts and foreign astronauts should have the same mood at that time.

Many people say that looking at the earth from space is a word "beauty". In fact, I think it should be more thorough. Some people think that if leaders of various countries go to space to see the earth, there may not be so many disputes. The idealist’s idea is really beautiful. "A leopard cannot change his spots." It’s not who is slipped into space that can change the world pattern. The greed, desire and the law of the jungle of human nature can only become more and more obvious. How can it become more and more noble? People are not gods. If human beings could have a bigger pattern, the world would have been in harmony. Maybe all mankind will be promoted to the first level of civilization through cutting-edge technology.

The greedy desire that human beings can’t change is just like dogs can’t change eating shit and cats love eating fish. It’s hard to hear, but it’s true. Humans always think that the world belongs to me, and they are always willing to position themselves as masters and creators, so their possessiveness is very strong. As long as they rule a country or rule the earth, everything on the earth is not ours. Perhaps all this is not human’s original intention. Humans can only control their own behaviors and desires, but can’t decide higher-level things at all. In fact, it is God’s will that created human beings and human nature, which cannot be easily changed by human beings. In fact, we are more like the "turkey" in the TV series "Three-body".

Maybe you think I’m talking nonsense, but isn’t Einstein talking nonsense? Does Yang Zhenning, a Chinese scientist, also talk nonsense? They all tend to be God-like creators, so I said that there is no problem with the existence of God. When I say providence, I mean God! Although Einstein and China’s Yang Zhenning are both great scientists and physicists. Celebrities, maybe they are just idols of human beings, with a little more ideas. As carbon-based human beings, we have not yet broken through the first-class civilization of the universe. How can you see through so many things? Perhaps, as I said before, this phenomenon is called a myth before the problems that human beings can’t solve.

We pay attention to science in everything, but how did Einstein and Yang Zhenning get the first driving force before the Big Bang in the universe? That’s why everyone is more inclined to have a creator. Maybe the first driving force of the universe is naturally formed, and what about the driving force of natural release? Personally, I think that life on our earth basically belongs to carbon-based life. The study of all the stars and various phenomena in space is only in one point to observe the whole universe. If we humans move towards the first level civilization, we may know more mysteries in the universe and space.

For the vast mysteries of the universe, human beings are nothing more than children picking up shells at the seaside. It may be a joke to look back at human science when we enter the next level of civilization, because all phenomena of celestial bodies are just guesses, so we can only rely on guesses. If a policeman solves a case by guessing, then we all know that this is nonsense. Isn’t it nonsense for a scientist to guess? Therefore, I think that after the arrival of silicon-based life, the theories of these scientists are all fart or even worthless. At least silicon-based life can go to danger in person and engage in scientific research in space.

Silicon-based life is more suitable for all kinds of harsh environments, and perhaps no oxygen mask is needed at all. I think these great scientists live by guessing there, so it is better to study life and make human life stronger and wiser. Only we are strong. We can conquer a more powerful universe. Humans may not understand it, but human wisdom has developed artificial intelligence robots, which will not be tired or sleepy. Maybe artificial intelligence will tell us in the future.

Meta develops multi-functional tactile skin Reskin, which can quickly improve machine sensitivity and tactile sensing accuracy.

Nowadays, artificial intelligence gradually integrates with human senses such as sound and vision, making communication between people more convenient. However, it is still challenging to integrate artificial intelligence with human touch.

To solve this problem, Meta AI and Carnegie Mellon University successfully developed a multifunctional, replaceable and durable tactile skin, and named it ReSkin. It can quickly improve the tactile sensing accuracy and sensitivity of the machine in the application process.

(Source:Proceedings of Machine Learning Research )

Related papers are titled "Multifunctional, Replaceable and Lasting Touch Skin" (ReSkin:versatile,replaceable,lasting tactile skins) published in [1].

Matai visiting researcher Raunaq Buhanlan (Raunaq Bhirangi), Tess Hellebrex, postdoctoral fellow and scientist of Pittsburgh Yuan Artificial Intelligence Research Center (Tess Hellebrekers), Mel Majdi, Professor of Robotics Institute of Carnegie Mellon University (Carmel Majidi), Abbina Gupta, Associate Professor of Robotics Institute of Carnegie Mellon University (Abhinav Gupta) is the author of the paper.

It is mentioned in the paper that ReSkin relies on machine learning technology and magnetic sensing technology, and has the advantages of being cheap, multifunctional, durable and replaceable. Specifically, ReSkin has low production cost, only 2-3mm thick, and can interact with the machine model more than 50,000 times. In addition, it has a high spatial-temporal resolution with an accuracy of 90%.Figure | ReSkin is the size of a coin and easy to manufacture (Source:PMLR)

Thin and high-precision specifications make it suitable for all kinds of machines, such as robot hands, tactile gloves, arm sleeves, etc. For specific processes, ReSkin can also provide tactile signals (high frequency) for the sliding, throwing, catching and clapping operations of the machine.

When Reskin is applied to different products, a large amount of relevant data will be generated, which can help researchers improve their tactile perception ability in AI systems.

Figure | Frequency changes of sensors applying ReSkin under different magnetic fields (Source:Proceedings of Machine Learning Research)

For example, ReSkin is an elastic sheet that can change its shape and contains magnetic particles. When its shape changes, it will release different magnetic signals. Researchers can use magnetometer to measure these changes, and use data-driven technology to convert the measured data into information such as contact position and applied force.

At present, many tactile sensing experiments are limited to one sensor level. This is because every time ReSkin is replaced, the machine needs to build a new model, which reduces the transmission efficiency.

Moreover, in different scenarios, each sensor needs to be thoroughly calibrated with the initial calibration procedure to match its individual response. This means that the calibration procedure must also adapt to these changes. In addition, as time goes by, soft skin like ReSkin will deform and need to be replaced, so it is difficult to popularize and apply it to different interactive scenarios.

In order to simplify the replacement process of Reskin, the researchers made innovations in three aspects.

Firstly, the researcher separates the internal circuit of the sensor from the passive interface, and does not need to be electrically connected with the traditional measuring electronic equipment. This operation effectively improves the sensitivity of the sensor, and it is as simple as sticking a sticker when replacing worn ReSkin.

Secondly, the researchers use the output data of several sensors to improve the model mapping. Through this operation, researchers can use a higher data diversity training model. This helps the sensor to output more effective and generalized data.

Thirdly, thanks to the self-supervised learning mode of the machine, researchers don’t have to collect calibration data for each new sensor, but use a small amount of unlabeled data to automatically fine-tune the sensor.

It is known that the existing camera-based tactile sensor requires a very small contact distance between the surface and the camera, which leads to a heavier machine. In contrast, ReSkin can cover the hands and arms of humans and robots, which facilitates researchers to develop multifunctional, expandable and inexpensive tactile modules. This is impossible for the existing artificial intelligence tactile system.

In order to highlight the practical value of ReSkin and show its unique charm of promoting the development of artificial intelligence, researchers apply it to different machine scenes. From grasping tiny objects to measuring the force exerted by the dog’s feet, from building a continuous ReSkin with wide coverage to measuring the field contact force, ReSkin has shown its extremely high flexibility and practicality.

Although researchers have demonstrated the technical advantages of ReSkin in contact location and force prediction, ReSkin still has great development potential in the future.

The experiment of this paper is based on the single-point contact of the machine, and the goal of scientists is to further study the application of ReSkin under multi-point contact. Another interesting future development direction is to specifically analyze the influence of external magnetic field and metal objects on ReSkin’s perception ability.

In addition, based on ReSkin’s high time resolution of 400Hz, researchers can make use of this advantage and use dynamic time series data to create better machine models. In a word, scientists believe that ReSkin will promote the machine’s touch perception ability and be able to apply it to practice.

References:
1.https://arxiv.org/abs/2111.00071
2.https://ai.facebook.com/blog/reskin-a-versatile-replaceable-low-cost-skin-for-ai-research-on-tactile-perception/

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 ~