0

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"

  • Are we talking integers only? By "between 20 and 24" do mean "larger or equal to 20 and smaller or equal to 24"? – cybernetic.nomad Mar 21 at 19:08
  • I mean larger or equal to 20 and smaller or equal to 24. – Tie Mar 21 at 19:12
  • Questions about Excel, such as this one, would be so much cleaner if SuperUser had TeX/MathJax. I'm hoping that StackExchange might consider adopting MathJax more widely in the future. – Nat Mar 21 at 22:12
4

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.

  • Hello! Can you give me the formula for the below! I tried to use the above format but I kept getting an error. – Tie Mar 22 at 5:32
  • If cell f2 says "Core State Inc. - USA Semi-Monthly 1" then I want 000 to be entered into the cell If cell f2 says "Core States Construction Services - USA Semi-Monthly 2" then I want 001 to be entered into the cell If cell f2 says "Integrated Images" then I want 002 to be entered into the cell Thank you, – Tie Mar 22 at 5:33
  • 1
    For that scenario, use the following: '=IF($F$2="Core State Inc. - USA Semi-Monthly 1","000",IF($F$2="Core States Construction Services - USA Semi-Monthly 2","001",IF($F$2="Integrated Images","002","Error")))' I assumed an absolute reference for F2, but you might have to adjust that depending on how you're using your spreadsheet. Note: Setting up the formula in this manner (using an if/else cascade) works well enough for small amounts of data and a small number of cases (yours had 3). If you plan on adding more than 3 ranges to check, you should definitely use cybernetic.nomad's method. – user2800 Mar 22 at 11:55
  • Thank you! You’ve been a great help! – Tie Mar 22 at 12:22
5

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

  • Hello the numbers in cell u2 are ages. I have to turn them into ranges so for people who are aged 20-24, I need it to enter in 20-24 EE and if they are aged between 25-29, I need it to enter in 25-29 EE and so on. – Tie Mar 21 at 19:30
2

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.

Solution

sample data

1. Create and name your lookup table

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':

lookup table

2. Formula

=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 2nd column. The TRUE argument is what makes it work as a lookup table.

NB.

This is dead easy, as, again, it's exactly why VLOOKUP exists. The advantage of using a lookup table over a series of nested IFs 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.

  • 2
    There's something unsatisfying about needing to build an extra table on the spreadsheet, as recommended by this answer. As a consequence, I suspect that a lot of users wouldn't like this approach. Still, this approach seems a lot more maintainable, legible, and just generally well-constructed. As such, I probably wouldn't bother doing it this way for a quick calculation that's just going to be tossed anyway, but for creating a professional spreadsheet that'll endure and be shared, this seems like the way to go. – Nat Mar 21 at 21:58
  • 1
    @Nat I totally agree. If I needed a one-off and was doing it myself for myself, there's zero chance I'd set it up this way. Probably three IFs and definitely fewer, I'd just write nested IFs; 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
  • @Tie very happy to help. Out of curiosity, what solution did you go with, and how has it worked out for you? – Alex M Mar 21 at 22:41
  • I used =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"))) – Tie Mar 21 at 23:12
  • Thank you User280! – Tie Mar 21 at 23:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.