Choose a number group from a single number input.

I have nine Age Groups: 14-17, 18-24, etc. upto 85-94 and they occupy nine cells B1 to J1. If I input an age, say, 22 in cell A1, I would like the relevant Age Group, '18-24', displayed in A2. This would apply to any entered age from 14 to 94 which would result in the appropriate Age Group displayed.

I probably could use loads of nested 'IFs', but is there a simpler way to achieve this?

I've just installed Excel 2019 a few weeks ago, so I'm not upto speed with any of the latest functions - yet.

Reagards,

Alan

You really don't need Excel's latest functions for this - an old favourite does the job.

You need to create a lookup table that consists of the first age in each band on the left and the age band itself on the right, then use a VLOOKUP:

Excel 2016 (Windows) 32 bit

	C	D
27	4	4-17
28	18	18-24
29	85	85-94
30
31
32	22	18-24

Sheet: Test

Excel 2016 (Windows) 32 bit

	D
32	=VLOOKUP(C32,$C$27:$D$29,2,1)

Sheet: Test

Hey Alan,

I call this problem a VLookup with the last argument as TRUE. Read about it at:
https://www.wallstreetmojo.com/vlookup-with-true/
The "1" in Ali's formula above means True. Note: You need to have your data sorted from small to large to use VLookup(,,,True)

As I understood the question, I would have expected VLOOKUP(...,TRUE) would have worked for you, and your latest post does not explain to me why it does not work. Do you have a specific example where it doesn't work so we can see why it doesn't work? That would help us come up with an alternative formula.

Alan - there is no reason why my solution won't work based on your latest description. To be sure, please attach a workbook with the layout that you are dealing with and I will advise you how to implement it. Instructions at the top of this page.

The only difference between your latest example and AliGW's example is that your data are arranged horizontally (where her data are arrange vertically). Same principle applies, only use the HLOOKUP() function instead of the VLOOKUP() function:

1) Enter 14 in D5, 18 in E5, 25 in F5, and so on.
2) Enter your lookup value in N5
3) Enter your HLOOKUP(...,TRUE) function in N8 =HLOOKUP(N5,D5:L10,2,TRUE)

Does that work?

Try this:

=LOOKUP(N5,VALUE(LEFT($D$6:$L$6,2)),$D$6:$L$6)

Then in N11 copied down:

=INDEX($D7:$L7,MATCH($N$8,$D$6:$L$6,0))

Yes, that’s correct in the latest versions of Excel, and you have 2019.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.