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. assign a category 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. 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. Functions Used

Used for Functions Used Functions Used Used for Used for IF

To perform logical tests and return different values based on whether the test is TRUE or FALSE. IF IF To perform logical tests and return different values based on whether the test is TRUE or FALSE. To perform logical tests and return different values based on whether the test is TRUE or FALSE. TRUE 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. VLOOKUP VLOOKUP To look up a value in the first column of a table and return a value in the same row from another column. To look up a value in the first column of a table and return a value in the same row from another column. first column return a value IFS (Later 2019 or Office 365)

To test multiple conditions and return a value corresponding to the first TRUE condition. IFS (Later 2019 or Office 365) IFS (Later 2019 or Office 365) To test multiple conditions and return a value corresponding to the first TRUE condition. To test multiple conditions and return a value corresponding to the first TRUE condition. multiple conditions 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. IF logical test one or more values true 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. Poor Fair Good, The used formula is, =IF(C4<=200, "Poor", IF(C4<=400, "Fair", IF(C4<=600, "Good", "Excellent"))) =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“. If the input value is 200 or less, it shows “Poor“. Poor If it’s more than 200 but 400 or less, it shows “Fair“. Fair If it’s more than 400 but 600 or less, it shows “Good“. Good If it’s more than 600, it shows “Excellent“. Excellent Note: This method is suitable for short conditions and small datasets in Excel worksheets. 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. VLOOKUP mark ranges performance 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) =VLOOKUP(C4,$F$4:$G$7,2) C4: This is the cell containing the value you want to look up. C4: This is the cell containing the value you want to look up. C4: cell containing the value $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. $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. $F$4:$G$7: VLOOKUP 2: This tells Excel to return the value from the second column of the lookup range. 2: This tells Excel to return the value from the second column of the lookup range. 2: 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. IFS function need to repeat the function The formula used here is: =IFS(C4<=200,"Poor",C4<=400,"Fair",C4<=600,"Good",C4>600,"Excellent") =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. 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. IF Function

IFS Function IF Function IF Function IFS Function IFS Function Checks one condition at a time.

Checks multiple conditions in a single formula. Checks one condition at a time. Checks one condition at a time. one condition Checks multiple conditions in a single formula. Checks multiple conditions in a single formula. multiple conditions To check multiple conditions, nested IFs are needed, which can get complex.

No need for nesting, easier to read and write. To check multiple conditions, nested IFs are needed, which can get complex. To check multiple conditions, nested IFs are needed, which can get complex. nested IF No need for nesting, easier to read and write. No need for nesting, easier to read and write. nesting Available in all versions of Excel.

Available only in Excel 2016 and later. Available in all versions of Excel. Available in all versions of Excel. all versions of Excel Available only in Excel 2016 and later. Available only in Excel 2016 and later. Excel 2016 and later Can return a value if TRUE and another if FALSE.

Can return different results for each condition. Can return a value if TRUE and another if FALSE. Can return a value if TRUE and another if FALSE. TRUE FALSE Can return different results for each condition. Can return different results for each condition. different results for each condition Harder to manage when there are many conditions (nested IFs).

Easier to manage when there are many conditions. Harder to manage when there are many conditions (nested IFs). Harder to manage when there are many conditions (nested IFs). Harder to manage Easier to manage when there are many conditions. Easier to manage when there are many conditions. Easier to manage More flexible in certain cases when you need only one condition.

More efficient for evaluating multiple conditions simultaneously. More flexible in certain cases when you need only one condition. More flexible in certain cases when you need only one condition. More flexible More efficient for evaluating multiple conditions simultaneously. More efficient for evaluating multiple conditions simultaneously. More efficient That’s it. This tutorial was originally published on How Can You Assign a Value/Category based on Number Range in Excel? How Can You Assign a Value/Category based on Number Range in Excel

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Range

How Do I Find the Cell That Contains the Maximum or Minimum Value in Excel?

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Excel Formulas for Assigning Categories by Value Range Using Functions!

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

How Do I Find the Cell That Contains the Maximum or Minimum Value in Excel?

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

How Do I Find the Cell That Contains the Maximum or Minimum Value in Excel?

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps