Forum Discussion

swexcelnurd's avatar
swexcelnurd
Copper Contributor
Mar 09, 2022
Solved

Formula Help

Can someone please help me with the formula for the attached.  I got the instructions/info from the group but it doesnt seem to be working. My formula is returning the same level for every student in...
excel
  • mtarler's avatar
    mtarler
    Mar 09, 2022

    swexcelnurd  You can use Riny_van_Eekelen solution which circumvents the whole lookup but if you still want to use a lookup function i have attached your data (copied from your link but the formatting changed a bit) showing that the formula does work.  i also added an extra column to give the 'look' of the Age range 5-10, etc.. 

    OR you could use the 2nd column to display the age range "5-10" and hide the first column with the 5, 11, and 17 in it.  You could create a formula that would even pull the numbers from your text out, but to me that is just overly complicated for this purpose.

    As for why it didn't work for you, i noticed triangles in the age cells.  Is it possible those were entered as TEXT instead of numbers?  if not those, is it possible the Age column was entered as text?  otherwise I'm not sure.

mtarler's avatar
mtarler
Silver Contributor
Mar 09, 2022
Yeah there are a couple of problems with that formula. First and foremost is that Excel does not understand what 5-10 means. It is just text characters. Second, for Vlookup the lookup range must be in column 1. Since you didn't provide what version of excel you have I will stick with Vlookup BUT if you are using Excel 365 I would recommend using XLookup.
Redo the 'STAGES OF LEARNING' table flip-flopping LEVEL and AGE to be:
5 1
11 2
17 3
note that anything less than 5 will return #N/A and anything larger than 17 (e.g. 19) will still return 17-18 range (i.e. 3). If you wan't an N/A or something for 19+ then add a row
19 N/A
Next fix the formula to point at that range but with a ,2, so:
=VLOOKUP(G4,$T$4:$U$6,2,TRUE)
  • swexcelnurd's avatar
    swexcelnurd
    Copper Contributor
    Mar 09, 2022

    Thanks so much for the promp reply mtarler 

    I have done that and seem to still have the same problem

    I am using Excel 365

    • BK_Bangkok's avatar
      BK_Bangkok
      Copper Contributor
      Mar 09, 2022
      can you post the file here, I will write the formula for you and then repost it.

      create a copy and then erase the identifying information then post it
      • swexcelnurd's avatar
        swexcelnurd
        Copper Contributor
        Mar 09, 2022
        https://docs.google.com/spreadsheets/d/e/2PACX-1vR6bcLsIoni07XenbCA8kb7fBPb3sxAgL8aJ43su-iLPU7OBMed1puGPcCqD72f63rkpJTGBt4Ssinz/pubhtml

Resources