Shulou(Shulou.com)11/24 Report--

Original title: "good-bye, VLOOKUP!" This set of CP functions is the king of search! "

Hello, Hi~, I am Tian Tian, who loves to learn from Excel.

It's time for [0 basic learning Excel function] to start the class on time every week.

In previous studies, we have learned the basic usage of match function and index function.

The match function can find the location based on the object, while the index function can find the object according to the location, so the two are complementary to each other, so the two functions are usually used together.

Today we are going to learn the usage of index + match function.

1. Index + match function Let's look at this case: now look for the corresponding score in the score table according to the examinee's name and subject in the row and column title.

First of all, we have to find out the corresponding position of the name and subject in the transcript. The match function is used here.

❶ enter = MATCH in the [A15] cell and press [Tab] to quickly insert the function and parentheses "(".

Now we want to find Kong Juan, select the cell [B15]; look for it in the name column of the grade table, so select the cell [A2:A7].

The result of the search should be accurate, so enter the number "0" in the third parameter with anti-parentheses ")".

Don't forget to reference the second parameter absolutely, select the second parameter [A2:A7] and press F4 to quickly lock. After entering the formula, press the enter key and fill in the formula down, and the corresponding rows of these names in the score table will be queried.

The formula is as follows:

= MATCH (B15) ❷. Next, we will find out the corresponding position page of the discipline in the score sheet according to the method just now.

Enter = MATCH in the [C13] cell, with "(" parentheses, the first parameter selects the mathematical [C14] cell, and the second parameter selects the subject row [B1:G1] cell of the score table.

The third parameter of ❸, enter the number "0" to indicate precise search, enter anti-parenthesis ")", use [F4] key to lock [B1:G1], press enter key, and fill in the formula to the right. The corresponding columns of these disciplines in the score table are also queried.

The formula is as follows:

We have found out the line number and column number corresponding to the name and discipline, and now we need to quickly find out their corresponding scores according to the line number and column number found. Here we can use the index function to do this.

❶ enters = INDEX in the [C15] cell, and the first parameter is the search range. We select the entire fraction range [B2:G7] cell.

❷ the second parameter is to find the row [A15] cell, the third parameter is to find the number of columns, select [C13] cell, put on the anti-parentheses, press the enter key, Kong Juan's math results, we query out.

❸ is going to populate the formula next. Note that relative and absolute references should be considered when populating the formula.

At this time, you have to imagine the next picture in your mind: this formula refers to a total of three cell regions. When we fill to the right, only the third parameter that represents the column can move with it. When we fill down, only the second parameter that represents the row can move, while the first parameter that represents the region is always fixed.

❹ so we try to modify the formula by double-clicking the formula cell to enter formula editing mode.

For the first parameter, we use the F4 key to quickly lock.

For the second parameter, we lock their columns by adding the dollar sign "$" directly to [A15] [C13].

In the third parameter, we add a dollar sign directly before [13] to lock their line "$". After the modification, fill the formula to the right, and then fill the formula down, and the results will be queried.

The formula is as follows:

= INDEX ($Bath2VERVERG7 recording A15 INDEX 13) of course, if you don't want to keep the auxiliary data, you can combine the match and index functions to 👇.

The complete formula is as follows:

= INDEX ($Bath2VOULGO7 match), MATCH (C14)) can learn Excel~ well by seeing more and practicing more.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Tian Tian

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.