How Can You Assign a Value/Category based on Number Range in Excel?

Using Excel Formula to Assign Categories to the Number of Ranges in Excel!

Narendhiran Vijayakumar 2 days agoLast Updated: June 4, 2025

0 10 3 minutes read

Assigning Category to the Number Range Using IF Function:
Assigning Category to the Number Range Using VLOOKUP Function:
Assigning Category to the Number Range Using IFS Function:

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 you the three working methods to Assign a category or value based on the number range in Excel.

The functions used in this tutorials 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 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.

Using the IF function to assign performance categories to students based on their total marks. — Using the **IF function** to assign performance categories to students 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 the short conditions and small datasets Excel worksheets.

Assigning Category to the Number Range Using 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.

Create helper table for assigning the category using the VLOOKUP Function.

Now, I’m going to use the Excel formula to assign the performance category based on the total marks of the students using 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 assigned the performance category to the students based on the marks with the help from the helper table.

Using the VLOOKUP function to assign performance categories to students based on their total marks. — Using the **VLOOKUP function** to assign performance categories to students based on their total marks.

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 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")

Using the IFS function to assign performance categories to students based on their total marks. — Using the **IFS function** to assign performance categories to students based on their total marks.

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.

–Narendhiran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.

Recently Published Tutorials: