I need your assistance with this. (Power BI or Excel)

I’m trying to write a formula for this table that will show the subsequent value if a row containing the same user has “no type” as its type.

When Orange in column A, for instance, has a type of “no type” in column B, the result in column D will show “milk” since there is another Orange in row 4 that does not have a “no type” in column B.
If “no type” is selected, the value will remain unchanged.

In addition, the following user (apple, for example) will only replicate the user’s type underneath it rather than the value above.

ColA ColB ColD
Name Type Result
Orange No Type Milk
Orange No Type Milk
Apple Juice Juice
Orange Milk Milk
Orange No Type No Type
Apple No Type Pie
Grapes Wine Wine
Apple Pie Pie
Apple No Type No Type

I tried using this formula,

=XLOOKUP(1, ([Name]= [@Name])*([Type]<>”No Type”),[Type], [Type])

However, all of the “No type” are changed regardless whether they are above or below.

ColA ColB ColC ColD
Name Type MyFormula(not correct) Expected Result
Orange No Type Milk Milk
Orange No Type Milk Milk
Apple Juice Juice Juice
Orange Milk Milk Milk
Orange No Type Milk No Type
Apple No Type Juice Pie
Grapes Wine Wine Wine
Apple Pie Juice Pie
Apple No Type Juice No Type
Banana Shake Shake Shake

Please help me if it is doable in Excel or Power BI Thank you in advance. 🙂