Populating if numbers are in a column

[Gummyworms1234]

Hello everyone,

I have a simple question which has me stuck for some reason. I have posted a picture of what I am trying to do. I want the stages of life kids are in to automatically populate based on their age but I can't figure out the formula to use. I posted a screenshot of what I am trying to do. For example, the stage Bob would be in is grade school, Susan would be in grade school, Steven would be in pre school and Mike would be a toddler. Thanks for the help!

 

[Marcelo Branco]

Try

Pasta1
	A	B	C	D	E	F	G
1					Stages
2	Name	Age	Stage		toddler	pre school	grade school
3	Bob	9	grade school		1	3	6
4	Susan	7	grade school		2	4	7
5	Steve	4	pre school			5	8
6	Mike	2	toddler				9
7							10
8							11
9							12
Plan7

Cell Formulas
Range	Formula
C3:C6	C3	=INDEX(E$2:G$2,AGGREGATE(15,6,(COLUMN(E$2:G$2)-COLUMN(E$2)+1)/(E$2:G$9=B3),1))

M.

 

[Micron]

Wouldn't this be easier if the stages sheet was like (simple Vlookup)?

1​	toddler
2​	toddler
3​	pre-school
4​	pre-school
5​	pre-school
6​	grade school
7​	grade school
8​	grade school
9​	grade school
10​	grade school
11​	grade school
12​	grade school

 

[Joe4]

I notice that you have age 5 under BOTH "pre school" and "grade school".
Is that correct, or is that a typo?
If correct, then do you want BOTH stages to show if the person is age 5?

 

[Gummyworms1234]

I notice that you have age 5 under BOTH "pre school" and "grade school".
Is that correct, or is that a typo?
If correct, then do you want BOTH stages to show if the person is age 5?

Sorry that was a typo. Having 5 under pre school is fine.

 

[Joe4]

Another way, using no lookup table or grid, and just imbedding the logic into the formula is the following.
Place this formula in cell C3:

=IF(B3>=6,"grade school",IF(B3>=3,"pre-school",IF(B3>=1,"toddler","unknown")))

 

[Gummyworms1234]

Another way, using no lookup table or grid, and just imbedding the logic into the formula is the following.
Place this formula in cell C3:

=IF(B3>=6,"grade school",IF(B3>=3,"pre-school",IF(B3>=1,"toddler","unknown")))

That would work but the range I have doesn't go in a typical 1, 2, 3 order. It jumps around randomly. One range could be 1, 6, 9, 24, 56 and 70 for example.

 

[Joe4]

That would work but the range I have doesn't go in a typical 1, 2, 3 order. It jumps around randomly. One range could be 1, 6, 9, 24, 56 and 70 for example.

OK, we can only base our solutions on the explanation and data that you present us (we aren't mind-readers!), and the example you posted was very linear with distinct cut-offs.

That actually is a good example why when posting questions here, you should not oversimplify it for our sake.
Otherwise, you may get solutions that work for the example/situation which you posted, but would not work on your actual data.
So it is always best to represent the actual scenario you have, and not oversimplify it.