Assigning a category based on a number range is a common practice everywhere in schools, colleges, and offices. You can do this in Excel, too. By using the right Excel functions and formulas, you can assign categories to values based on their range in the cells. In this tutorial, I will share with you the three working methods to assign a category or value based on the number range in Excel.
The functions used in this tutorial are:
|
Functions Used |
Used for |
|---|---|
|
IF |
To perform logical tests and return different values based on whether the test is TRUE or FALSE. |
|
VLOOKUP |
To look up a value in the first column of a table and return a value in the same row from another column. |
|
IFS (Later 2019 or Office 365) |
To test multiple conditions and return a value corresponding to the first TRUE condition. |
Assigning Category to the Number Range Using the IF Function:
The IF function in Excel helps you perform a logical test and return one or more values depending on whether the test is true or false.
Let’s say we have a table with a list of student names and their total marks. Now, we’re going to use the IF function to categorize their performance into three categories, such as Poor, Fair, or Good, based on their total marks.
The used formula is,
=IF(C4<=200, "Poor", IF(C4<=400, "Fair", IF(C4<=600, "Good", "Excellent")))
- If the input value is 200 or less, it shows “Poor“.
- If it’s more than 200 but 400 or less, it shows “Fair“.
- If it’s more than 400 but 600 or less, it shows “Good“.
- If it’s more than 600, it shows “Excellent“.
Note: This method is suitable for short conditions and small datasets in Excel worksheets.
Assigning Category to the Number Range Using the VLOOKUP Function:
Using the VLOOKUP function for this task is a professional approach. First, you need to create a helper table like the one shown in the image below. The second table should contain the mark ranges and their corresponding performance categories.
Now, I’m going to use the Excel formula to assign the performance category based on the total marks of the students using the VLOOKUP Function.
=VLOOKUP(C4,$F$4:$G$7,2)
- C4: This is the cell containing the value you want to look up.
- $F$4:$G$7: This is the range of cells where the VLOOKUP will search for the value. It’s “locked” with dollar signs, so it doesn’t change if you copy the formula.
- 2: This tells Excel to return the value from the second column of the lookup range.
Executing the above formula will instantly assign the performance category to the students based on the marks with the help of the helper table.
That’s it; this is how you can use the VLOOKUP Function to assign categories based on the inputs in Excel.
Assigning Category to the Number Range Using the IFS Function:
Using the IFS function to do this will remove the need to repeat the function, like we did with the IF function above.
The formula used here is:
=IFS(C4<=200,"Poor",C4<=400,"Fair",C4<=600,"Good",C4>600,"Excellent")
Difference B/W IF & IFS Function:
|
IF Function |
IFS Function |
|---|---|
|
Checks one condition at a time. |
Checks multiple conditions in a single formula. |
|
To check multiple conditions, nested IFs are needed, which can get complex. |
No need for nesting, easier to read and write. |
|
Available in all versions of Excel. |
Available only in Excel 2016 and later. |
|
Can return a value if TRUE and another if FALSE. |
Can return different results for each condition. |
|
Harder to manage when there are many conditions (nested IFs). |
Easier to manage when there are many conditions. |
|
More flexible in certain cases when you need only one condition. |
More efficient for evaluating multiple conditions simultaneously. |
That’s it. This tutorial was originally published on How Can You Assign a Value/Category based on Number Range in Excel?