Nested Excel Formula Modification Question

Question

I am trying to figure out whats wrong with a nested formula I created:

In the image you can see the three sections the formula is broken up into:

1. is grouping cells into buckets
2. is referencing a separate tab (city density scores) and selecting only if the value is less than or equal the value in that tab
3. is performing a lookup also referencing a separate tab, and pulling in that value as long as that condition is met.

There is a unique key linking all three tabs. If all three conditions/sections of the formula are met, the idea is to return the value "Select" and if not, "Don't Select." When I ran this formula in excel I'm getting "Don't Select" for cells that meet all #1,#2,#3 criteria, trying to figure out what I am doing wrong.

This is the formula I created. I feel its a small change that I need to make:

=IF(AND(OR(AM6="1 to 3 Bucket",AM6="4 to 7 Bucket"),$BK6<='City Density Scores'!$H$6), IF((VLOOKUP(BI,'Rank Competition Vetting'!$A$7:$F$182,5,0))<33%,"Select","Don't Select"))

Answer 1

Your formula logic is a little off. The correct form is _{(replace BI6 with whatever the correct cell is)}

=IF(AND(OR(AM6="1 to 3 Bucket", AM6="4 to 7 Bucket"), $BK6<='City Density Scores'!$H$6, VLOOKUP(BI6, 'Rank Competition Vetting'!$A$7:$F$182,5,0) < 33%), "Select", "Don't Select")

Lets break it down: you have 3 criteria, all of which must be true to return Select

You have

IF(AND(OR(#1a,#1b),#2), IF((#3,"Select","Don't Select"))

This will work if #1 and #2 are TRUE, and fail otherwise (returns FALSE) because you havn't specified a False clause for the outer IF.

You really want all 3 clauses in the AND term

IF(AND(OR(#1a,#1b),#2,#3),"Select","Don't Select")

Now, you say you still get a false negative for a case where you believe all 3 criteria are met. To me, this points to a data issue - perhaps some numbers formatted as text? In any case, using the Evaluate Formula tool will help. You say you are having trouble using it: the process is to

• select a cell containing the formula, then activate the tool
• press Evaluate button repeatedly
• at each click the underlined part of the formula is evaluated.
• read the Evaluation to see if it lines up with your expectation

Here's an example (of my formula) evaluated up to the point where the VLOOKUP is about to be processed