excelexcel-formulalookup

How to assign a category based on a number range?

I'm having a problem with Excel.

In my game, the hero has max level, which determines by its rarity. So a Common Hero can get only to level 40

I want to determine which rarity a hero is based on its level (for example: Level 1 is Common, Level 45 is Rare, Level 60 is Epic, etc.)

enter image description here

===========================

Here's what I tried, using VLOOKUP
1. Adding a sub column next to "Level Max", the value is the same as "Rarity"

2. Use the formula : =VLOOKUP(D3,$B$2:$C$13,2,TRUE)

3. However, the result is not what I expected, if player level is less than 40, it returns "N/A", if player level is, for example, 45, it returns "Common" (it should be Rare)

enter image description here

Solution

  • Try using INDEX( ) & MATCH( ) functions.

    enter image description here

    • Formula used in cell F3

    =INDEX($C$2:$C$13,MATCH(1,1/(E3<=$B$2:$B$13),0))

    Alternative approach using LAMBDA( ) helper function MAP( ) with XLOOKUP( ) the following formula will spill dynamically

    enter image description here

    • Formula used in cell F3

    =MAP(E3:E4,LAMBDA(m,XLOOKUP(1,1/(m<=B2:B13),C2:C13)))

    Approach using VLOOKUP( ) function

    enter image description here

    • Formula used in cell F3

    =VLOOKUP(TRUE,IF({1,0},E3<=B$2:B$13,C$2:C$13),2,0)

    All the above screenshots updated (initial update was from Android Mobile MS365) and formulas applied in MS365 Version Desktop Excel App, hence CTRL+SHIFT+ENTER was not required while exiting the edit mode, but it is needed based on ones excel version. Below screenshot shows formula applied in Excel 2010 using VLOOKUP( ) and INDEX( ) & MATCH( )

    With INDEX( ) & MATCH( ) so it should work in Excel 2013 as well, but remember to hit CTRL+SHIFT+ENTER (see those curly brackets at the end)

    enter image description here

    With VLOOKUP( ) in Excel 2010 so it should work in Excel 2013 as well, but remember to hit CTRL+SHIFT+ENTER

    enter image description here

    Last but not least, using LOOKUP( ) function is just succinct and you dont need to hit CTRL+SHIFT+ENTER while exiting the edit mode, only you have to create an additional column for Level Min

    enter image description here

    • Formula used in cell F3

    =LOOKUP(E3,$A$2:$C$13)

    Some facts about LOOKUP( ) Function:

    Also adding the approach with Modern Excel Function which I have shown earlier using MAP( ) & XLOOKUP( ) showing only with XLOOKUP( ) so it may be a useful resource for others seeking for a solution.

    enter image description here

    • Formula used in cell F3

    =XLOOKUP(E3:E4,A2:A13,C2:C13,,-1)

    Or,

    • Formula used in cell G3

    =XLOOKUP(E3:E4,B2:B13,C2:C13,,1)