I need to create a formula dependent on the value of cell U2
If it is between 20 and 24
set cell V2 = "20-24 EE"
or
If it is between 25 and 29
set cell V2 = "25-29 EE"
or
If it is between 30 and 35
set cell V2 = "30-35 EE"
I need to create a formula dependent on the value of cell U2
If it is between 20 and 24
set cell V2 = "20-24 EE"
or
If it is between 25 and 29
set cell V2 = "25-29 EE"
or
If it is between 30 and 35
set cell V2 = "30-35 EE"
Try this formula in V2:
=IF(AND($U$2>=20,$U$2<=24),"20-24 EE",IF(AND($U$2>=25,$U$2<=29),"25-29 EE",IF(AND($U$2>=30,$U$2<=35),"30-35 EE","Error")))
This will return "20-24 EE", "25-29 EE", or "30-35 EE" for integer values in the respective range. It will return "Error" for non-integer values or integers less than 20 or greater than 35.
Assuming you are dealing with integers only, you can use INDEX
and MATCH
:
=INDEX({"","20-24 ""","25-29 EE","30-35 EE",""},MATCH(U2,{0,20,25,29,35,10000000},1))
NB: The formula will return ""
if the number is less than 20 or more than 35
I'll take a different approach, because this IS the use case for which VLOOKUP
is designed. INDEX
MATCH
is indubitably more powerful, but it's needlessly complex here.
I put the lookup table in X:Y
. It can be a different sheet or workbook altogether, if you like. Put your delimiters in the first column and the group names you want in the second column. Highlight the table (no headers) and select 'Define Name':
=VLOOKUP(U2,groups,2,TRUE)
Copy that formula into V2
. That formula copies down.
This says look up U2
in the table called groups
(you could also skip step 1 and give this function a range in the form of cell references, X2:Y6
in this case), and bring back the value from the 2
nd column. The TRUE
argument is what makes it work as a lookup table.
This is dead easy, as, again, it's exactly why VLOOKUP
exists. The advantage of using a lookup table over a series of nested IF
s or an INDEX
MATCH
array formula (both totally viable solutions for this request) is 1) its ease of use due to the simplicity of the formula itself, but even more importantly 2) its flexibility moving forward. If the groupings change, or the group names change, or you need MORE groups or fewer, all you have to do is change the values in the lookup table; no formula entry is required at all.
IF
s and definitely fewer, I'd just write nested IF
s; more than that and I'd use the INDEX
MATCH
with arrays. Almost exactly how the other two answers here have it. I thought it'd be nice to present the entire range of approaches - and, as you say, if the use case calls for repeated use, or other people as users, this is definitely the way to go.
– Alex M
Mar 21 at 22:02