At the moment I am using the following formula to assign a score form 1-5 based on the number of opens and clicks an individual has:

=IF(F2<>"", (IF(AND(F2>=$Y$2,F2<$Y$3,G2=$Z$2),$X$2,(IF(AND(F2>=$Y$3,F2<$Y$5,G2>=$Z$3,G2<$Z$4),$X$3,(IF(AND(F2>=$Y$4,F2<$Y$5,G2>=$Z$4,G2<$Z$5),$X$4,(IF(AND(F2>=$Y$5,F2<$Y$6,G2>=$Z$5,G2<$Z$6),$X$5,(IF(AND(F2>=$Y$6,G2>=Z6),$X$6,"")))))))))),"")

Where F2 is the Opens and G2 is the clicks. The other values are shown in the screenshot below.

I know this isn't the most efficient (or effective) way to do it but I can't seem to figure it out. With this formula, if a person has, say, 13 opens and 12 clicks they wouldn't be assigned a score of 3 and would instead stay blank. Instead of it being unforgiving, I'd like it to look at the opens first to see if it meets any score criteria then assign a score based on how many clicks they have.

Any help with this would be amazing, I haven't been able to find anything too useful on google...

I could've described this a lot better... If they have 13 opens and no clicks they should be bumped down to a score of 1. If they have 4 opens and 20 clicks they would not get a score because it doesn't meet the minimum threshold.

So opens are a minimum requirement (can have 100 opens and still get a score of 1 if there are no clicks) while clicks can act as a minimum requirement as well as a ceiling.

E.g. a person with 14 opens and 2 clicks gets a score of 2 but 14 opens and 20 clicks gets a score of 3.

I hope this makes more sense!

CodePudding user response：

You can try:

=IFERROR(MIN(LOOKUP(B2,G:G,F:F),LOOKUP(C2,H:H,F:F)),"")