Forum Discussion
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 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
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.
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
- 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)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
- 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
