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:

is grouping cells into buckets
is referencing a separate tab (city density scores) and selecting only if the value is less than or equal the value in that tab
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"))

BI is a cell in the file. Its the unique key in that section of the formula used for the vlookup. — appreciateyourassistance
Try using Formulas > Evaluate Formula to see what the issue is. — BigBen
Thanks, have already been down that road, finding it hard to decipher what the excel evaluate formula results are saying, hence the reason I posted my question here. Really appreciate your help. — appreciateyourassistance
What does just VLOOKUP(BI,'Rank Competition Vetting'!$A$7:$F$182,5,0) return? — BigBen

chris neilsen chris neilsen · Accepted Answer · 2019-12-30T00:09:36

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

Nested Excel Formula Modification Question

1 Answers