Looking for Formula for excel

Question

ProductName | display | final value
Lewis           No
Lewis           No
Lewis           No
Sood            No
Henny           Yes
Henny           No
Henny           No

1.Looking for a formula in excel where if product have display value as yes with same product name then final value should come as yes and other should come as variant. 2.else if the product have display value no for all products with same name then it should come as not displayed.

Final output should be

ProductName | display | final value

Lewis           No.     Not display
Lewis           No.     Not display
Lewis           No.     Not display
Sood            No.     Not display
Henny           Yes.    Yes
Henny           No.     Variant
Henny           No.     Variant

Tried with if and count if but not able to find not displayed logic

Answer 1

In Office 365 you could use the following to spill the result:

=LET(range,A1:B8,
     r,ROWS(range),
     name,TAKE(range,1-r,1),
     display,TAKE(range,1-r,-1),
DROP(REDUCE(0, SEQUENCE(r-1,),
     LAMBDA(x, y, 
     VSTACK(x,
            IF( 
               ISNUMBER(XMATCH(INDEX(name,y)&"Yes",name&display)),         
               IF(INDEX(display,y)="Yes",
                  "Yes",
                  "Variant"),
               "No display")))),
     1))

In older Excel use: =IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,"Yes"),IF(B2="Yes","Yes","Variant"),"Not display")

It checks for the combination of the name and Yes being present anywhere in the range. If FALSE it results in Not display; if TRUE it checks if the value in column B is Yes, than it will result in Yes. Otherwise it will result in Variant.

Answer 2

Give a try on the following formula-

=LET(x,CONCAT(UNIQUE(FILTER($B$2:$B2,$A$2:$A2=A2))),IF(x="No","Not Display",IF(x="Yes","Yes","Variant")))