I'm trying to create a nested if statement in excel 2003 that will look for the value in a cell and return a name. In the example below, cell I2 contains "917R". This is "sorted" as a number. I'm guessing the reason its returning a '0" is because its not recognized due to the text. The result of the formula should be "Group3" is there anyway to make this formula or something else work that will give me the results I need? I have hundreds of numbers ranging from 0030 to 99ZZ. Thanks in advance!!

=IF(I2=30-3073,"Group1",IF(I2=3112-5420,"Group2",IF(I2=5498-9999,"Group3",)))

I'm going to start by correcting your if statement (at least based on what I think you are trying to do). Not sure how a number with the letter Z at the end is actually a number, usually that is text =IF(AND(I2>=30,I2<=3073),"Group1",IF(AND(I2>=3112,I2<=5420),"Group2",IF(AND(I2>=5498,I2<=9999),"Group3","OUT OF RANGE")))

I assumed that 30-3073 was a range you wanted your I2 to be in. what you had would have returned group1 only when I2 had a value of -3043.

I placed the "OUT OF RANGE" as a way of flagging something that does not fit into any of the three ranges. not sure if you can have a value of less than 30 or say a value of 3100.

my next question is how is 917R a number? Not something I am used to dealing with.

If this is all text, you could try

=IF(AND(I2>="0030",I2<="3073"),"Group1",IF(AND(I2>="3112",I2<="5420"),"Group2",IF(AND(I2>="5498",I2<="9999"),"Group3","OUT OF RANGE")))

and finally, is 9999 or 99ZZ larger? as you may need to edit the upper boundry for group 3

It might be possible to use VLOOKUP in combination with an IF statement and some text functions. Could you post a few more examples of your "hundreds of numbers" showing us different combinations of numbers and letters along with which Groups they belong in?

It looks like you jump from 3073 to 3112 and then from 5420 to 5498. Will there ever be values between 3073 and 3112 and/or 5420 and 5498?

In which range would something like 54ZZ fit?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll