IF Statement

[Gliffix101]

Hey All,

I am looking to design an IF statement that is pretty complicated. I've tried doing this with no success so far so I'm reaching out for help. Here is what I need it to say:

If H2 is not Blank AND P2 Is Between 13-54 Then Category 1 Else
If H2 is not Blank AND P2 Is Between 55-95 OR 170 then Category 2 Else
If H2 is not Blank AND P2 Is Between 100-150 Then Category 3 Else
If H2 is Blank AND P2 Is Between 13-54 Then Category 4 Else
If H2 is Blank AND P2 Is Between 100-150 Then Category 5

Notes:
* H2 and P2 are the cells in the first row. There are over 20,000 rows.
* H2 is a date OR is Blank
* P2 will always contain a number and is never blank.
* The Category output will be in Column T.

So far I've done some research and it's saying I need to use nested IF statements for each possibility. Well, trying to nest IF statements for all P2's between 13-54 would take FOREVER. I know there is an easier way so I'm reaching out to the experts.

I have not cross posted.

Bill

 

[tygrrboi]

If one of those 5 will always be true AND there are no numbers in P2 less than 13 or greater than 150 (with the exception of 170) then....

="Category "&IF(H2="",IF(P2<55,4,5),IF(OR(AND(P2<96,P2>54),P2=170),2,IF(P2<55,1,3)))

IF there is a possibility that there might be some combinations of H2 and P2 that do not meet any of your listed requirements (for example H2 blank and P2 170) then we need to come up with a different formula.

 

[Eric W]

You could also try something like:

="Category "&IF(H2="",LOOKUP(P2,{0,13,55,100,151},{0,4,0,5,0}),LOOKUP(P2,{0,13,55,96,100,151,170,171},{0,1,2,0,3,0,2,0}))

If the combination of H2 and P2 does not conform to one of your options, it will return 0. The ranges and category numbers are embedded within this formula, but if they are subject to change, you may want to build a table somewhere on your spreadsheet instead.

 

[Gliffix101]

Tygrrboi - this worked perfectly!! Exactly what I need it to do. Thank you.