Dividing corresponding values, scores, etc. into fixed ranges based on certain standards is also a frequently used operation in Excel. In addition to visual inspection, there are 6 methods to achieve this.

1. Excel level determination: IF function method.

Purpose: Determine "level" based on "score".

Method:

Enter the formula in the target cell: =IF(D3=100,"Full score",IF(D3>=95,"Excellent",IF(D3> =85,"Good",IF(D3>=60,"Pass","Fail")))).

Interpretation:

1. The IF function is the most common judgment function, and its grammatical structure is also very simple, =IF (judgment condition, return value when the condition is true, condition return value if false).

2. The nested form of IF function is used in this example.

2. Excel level determination: Ifs function method.

Purpose: Determine "level" based on "score".

Method:

Enter the formula in the target cell: =IFS(D3=100,"Full score",D3>=95,"Excellent",D3>=85," Good", D3>=60, "pass", D3<60, "fail").

Interpretation:

The function of the Ifs function is to check whether one or more conditions are met and return the value corresponding to the first True condition. The syntax structure is: =Ifs(judgment condition 1, return value 1, judgment condition 2, return value 2...judgment condition N, return value N), one condition corresponds to one return value.

3. Excel level determination: Vlookup function query method.

Purpose: Determine "level" based on "score".

Method:

Enter the formula in the target cell: =VLOOKUP(D3,H$3:I$7,2).

Interpretation:

1. The function of the Vlookup function is to return values ??that meet the conditions from the specified area. The syntax structure is: =Vlookup(query value, data range, return value column number,[match pattern]). When the matching mode is 0, it is a precise query, and when it is 1, it is a fuzzy query. Omitting the fourth parameter will default to fuzzy query when matching the pattern.

2. In the formula =VLOOKUP(D3,H$3:I$7,2), no matter which value is queried, the data range is H3:I7, so mixed reference or absolute reference is required.

3. When the Vlookup function uses fuzzy query, if the "query value" cannot be accurately matched in the data range, it will match the maximum value smaller than the query value.

4. Excel level determination: Lookup function query method.

Purpose: Determine "level" based on "score".

Method:

Enter the formula in the target cell: =LOOKUP(D3,H$3:I$7).

Interpretation:

1. The function of the Lookup function is to find values ??that meet the conditions in the specified row (or column, or area) and return them. Its syntax structure has two forms.

2. Vector form: Query the specified value in a single row or column, and return the value at the same position from the returned row or column. The syntax structure is: =Lookup (query value, the row or column where the query value is located, the row or column where the return value is located). However, when using vector form, the values ??of the row or column where the query value is located must be sorted in ascending order, otherwise the correct result cannot be obtained.

3. Array format: Return values ??that meet the conditions in the specified area. The syntax structure is: =Lookup (query value, data range). It should be noted that the query value must be in the first column of the data range, and the value to be returned must be in the last column of the data range.

5. Excel level determination: Choose+Match combination function method.

Purpose: Determine "level" based on "score".

Method:

Enter the formula in the target cell: =CHOOSE(MATCH(D3,H$3:H$7,1),I$3,I$4,I$5,I $6, I$7).

Interpretation:

1. The function of the Choose function is to filter out the corresponding value or perform the corresponding operation based on the specified value. The syntax structure is: =Choose(index value, return value 1 or expression 1, [return value 2 or expression 2]...[return value N or expression N]).

2. The function of the Match function is to return the relative position of the specified value in the specified range. The syntax structure is: =Match (positioning value, data range, match type), where there are three types of match types, namely 1 (less than), 0 (accurate), and -1 (greater than).

3. In the formula =CHOOSE(MATCH(D3,H$3:H$7,1),I$3,I$4,I$5,I$6,I$7), first use the Match function to extract the current value The relative position in H3:H7 is then extracted using the Choose function.

6. Excel level determination: Index+Match combination function method.

Purpose: Determine "level" based on "score".

Method:

Enter the formula in the target cell: =INDEX(I$3:I$7,MATCH(D3,H$3:H$7,1)).

Interpretation:

1. The function of the Index function is to return the value or reference at the intersection of the row and column from the specified area. The syntax structure is: =Index(data range, row, [column]). When the third parameter "column" is omitted, the default is the first column.

2. In the formula =INDEX(I$3:I$7,MATCH(D3,H$3:H$7,1)), first use the Match function to extract the relative position of the current value in H3:H7. Then use the Index function to extract.

Conclusion:

From the above study, we already know that there are many methods to determine the Excel level...The Excel functions used are also relatively common, easy to learn and easy to use. used.

If you have any questions during the learning process or have unique insights into determining Excel levels, you can leave a message in the message area for discussion!