Formula Help

[swexcelnurd]

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 instead of returning which level they are in for their age group listed in colomn G.  I want to then total each of the levels and add the total to colomn v

[#2]

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)

[#3]

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

[#4]

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

[#5]

@BK_Bangkok 

https://docs.google.com/spreadsheets/d/e/2PACX-1vR6bcLsIoni07XenbCA8kb7fBPb3sxAgL8aJ43su-iLPU7OBMed1...

[#6]

the age groups for A4-A6 really needs to say 5-10, 11-16 & 17-18

[#7]

https://docs.google.com/spreadsheets/d/e/2PACX-1vR6bcLsIoni07XenbCA8kb7fBPb3sxAgL8aJ43su-iLPU7OBMed1...

[#8]

@swexcelnurd You can leave the labels in A4:A6 as they are. Try the following formula. It hard-codes the age groups lower boundaries and levels. But since you only have three of such, that's not a big issue.

=LOOKUP(K4,{5,11,17},{1,2,3})

[#9 · Accepted solution]

@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.

[#10]

@BK_Bangkok @Riny_van_Eekelen @mtarler  Thank you so so much every one for your help.  Very much appreciated !!  I am sorted now

Ive learnt so much from your last reply @mtarler