Forum Discussion

Sergio Gonçalves's avatar
Sergio Gonçalves
Copper Contributor
May 23, 2017

Creating a level up system with Excel.

Hi. I'm trying to create a point based fitness log system where with each workout you earn points and with the points earned you level up over time. 

 

My issue is what would be the best way to do this besides using the "IF" function?

 

Thanks.

Formulas & Functions
Requests

6 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    May 23, 2017

    To get a level from a list of steps, first create a little table that looks similar to this:

     

    0Beginner
    50Intermediate
    100Pro
    200Expert
    1000Guru

     

    (lets assume this is on Sheet2, range A2:B6).

     

    Now suppose you have a # of points in cell D3, you can fetch the level from the table like so:

     

    =VLOOKUP(D3,Sheet2!$A$2:$B$6,2,TRUE)
    • GoldPlays's avatar
      GoldPlays
      Copper Contributor
      Mar 13, 2020

      JKPieterse I tried this with my own values, so when I put 100 in the points box and 100 points is 'Expert', it shows 'Expert' in the level name box. But when I put 105 in the points box, it shows #N/A. do you know what is wrong? Screenshot attached

      • Charla74's avatar
        Charla74
        Iron Contributor
        Mar 13, 2020
        Your formula shows FALSE at the end where it should show TRUE........the false flag looks for an exact match for the lookup.
    • Sergio Gonçalves's avatar
      Sergio Gonçalves
      Copper Contributor
      May 23, 2017

      Thanks so much Jan. Works like a charm. Don't quite understand how, but it does.

       

      Thanks 

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        May 24, 2017

        The VLOOKUP function looks up a value in a list of values.

        The last argument of the function is really important. If you set that to TRUE (as in our example) VLOOKUP *only* allows lookups in a (ascending) sorted list and if it does not find an exact match, it returns the item that is the first one less than the value you are looking for. So look for 9 and it will return the item belonging to 0. Look for 999 and it'll return what belongs to 200.

         

        If however you use FALSE as the final argument, VLOOKUP will only return EXACT matches and #N/A! if it does not find a match. Also, the table does not have to be sorted. FALSE is the most used argument, but in your case it is TRUE we needed.

Resources