Finding value based on multiple criteria-CodePudding

Can someone help me, I'm trying to find the value for ColE if is an "Individual", "Company" or "Mix" based on ColB if it has a "Y" flag.

Policy_1 only has a single row and the value for B2 is blank, is an Individual in ColE.

Policy_2 only has a single row and the value for B3 has "Y" flag, it is Company in ColE.

Policy_4 has two rows, B6 has "Y" flag and B7 is blank, it is Mix in ColE

CodePudding user response：

With Excel-365 can try-

=IF(COUNTA(UNIQUE(FILTER($B$2:$B$7,$A$2:$A$7=A2)))>1,"Mix",IF(COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,"Y")>=1,"Company","Individual"))

CodePudding user response：

Just happened to stumble on here. I think what you are looking for are the Inline IF Logical operators, and you can nest them. Like this:

Logical IF:

=IF(B3 = "Y",E3, "Not found!")

Logical IF, Nested:

=IF(B3="Y",( IF(D3="Carter", E3="This is for Carter only!", E3) ),"Not found")

Here is how you get the Row Number for a given cell:
ROW() for current cell ROW() is used in.

So then you can use it like this:

=IF("B" & ROW()="Y","Is a Y!!! here is the value! & "E" & ROW(), "(not a Y!!)")

Hope this helps!