Many people know vlookup function and hlookup function, but they know very little about lookup function. In fact, it also has very powerful functions. This article mainly describes its classification mark function and search function.
Classification function
The data in the chart is written down casually, and there is no rule for everyone to see. Today I want to introduce the use of LOOKUP formula to classify these irregular data. The larger the range of data, the more and less data can be classified. These can be classified according to our needs when analyzing data.
Select the cell where we want to present the result, click the FX position above the table, and the Insert Function box will pop up automatically. Select all functions and then enter LOOKUP at the search function. The LOOKUP function option will appear below the function selection. Under the LOOKUP function, there will be an explanation of the usage of the LOOKUP function. Find a value from a single row or column or data group. Finally, select the function and click OK.
The LOOKUP function interface box pops up. You can see that LOOKUP has three parameters. The first parameter is the search value, which is well understood as the value we need to search. I filled in A39 here, which means A39 is the value I need to search.
The second parameter is the search vector. The explanation on the function interface is that the cell range only contains single row and single column, which is much the same as the search vector. It's complicated to understand here. In fact, a simple understanding refers to a data segment. It should be noted that the vector should be enclosed in curly braces. As shown in the figure, you must pay special attention when filling in, otherwise you cannot use this formula to get the correct result.
Here I classify the second parameter with 10 as the data distance. When you use it, you can choose the distance between the data according to your needs. The third parameter is the segment corresponding to the second parameter, which is divided into 6 segments: extremely poor, poor, general, medium, excellent, and extremely excellent. The corresponding six sections are 0-13, 14-23, 24-33, 34-43, 44-53, 54-63 of the second parameter. The second parameter and the third parameter must have a corresponding relationship, which is what you need to know.
After filling in the three parameters of the LOOKUP function, click OK, and the classification of the number 13 will come out. You can see that it is extremely poor, and it is correct in the first classification. It was also said in the previous formula that one right does not mean that all are right.
Finally, we verify the formula and drag the formula down with the mouse, so that all the data corresponding classifications are available. We can check them one by one. Obviously, they are all very correct. That's all for the use of this formula today. You can go down and try it, hoping it will help.
Find function
We need to find out who the 75 students are in the table.
Enter=LOOKUP (E2, B2: B8, C2: C8) in F2 cell
Enter to get the following results. It is obviously correct.
The meaning in parentheses of the lookup function is that E2 is the score condition of our query, B2: B8 is the column interval of the score item, also called the condition area, and C2: C8 is the corresponding area of our search.