IF Statement Multiple Criteria and Threshold Ranges

[jws]

I am trying to do an IF statement that triggers a “review” flag based off multiple criteria mentioned below.

• Column N will be my calculated formula area
• Column M “Bucket” is a calculated field that is hidden as that is driven off criteria area in yellow

What I want to have is “IF “bucket” equals “statement in yellow” AND is >= to Column R and <= to Column S, then REVIEW. I don’t think I am explaining it correctly but if you see the example below it might be more clear.

If all of the “Buckets” were the same I could just paste this down the sheet but the bucket is driving the “review” flag based on different thresholds and this sheet is 20,000+ lines so filtering would be an option but I believe there is a better way.

Sample Formula if all buckets were “111 to 130”
=IF(OR(L11>=50%,L11<=-50%),"review","")

Ideally there is one formula that changes the thresholds based on the bucket. If there is more info needed please let me know, thanks in advance.

 

[shg]

Maybe

=IF(ABS(L11) <= VLOOKUP(M11, $Q$2:$R$8, 2, FALSE), "review", "")

 

[jws]

Thank you, the absolute value was something I overlooked - perfect!

=IF(ABS(L11)>= VLOOKUP(M11, $P$2:$R$8, 2, FALSE), "review", "")

Now I want to remove #N/A and I am getting an error with this:

=IF(ISNA(ABS(L11)>= VLOOKUP(M11, $P$2:$R$8, 2, FALSE), "review", ""), "", IF(ABS(L11)>= VLOOKUP(M11, $P$2:$R$8, 2, FALSE), "review", "")

I am sure I am overlooking something here...

 

[shg]

Why would there be values in the bucket column that are not in the table?

Sometimes an error is just that, an error.

 

[jws]

Sorry I guess I left that out. With the absolute value of 4 there are item numbers/lines that I am excluding from the results and would ideally like to have these errors out of the mix.

 

[shg]

=IFERROR(IF(ABS(L11)>= VLOOKUP(M11, $P$2:$R$8, 2, FALSE), "review", ""), "")

 

[jws]

Thank you very much, this works.

 

[shg]

You're welcome, glad it worked for you.