Identification of missing value for gender column based on previous known values

[absherzad]

Hi everyone,
Here is my question:
I have two columns "Name", and "Gender". There are duplicate names, for some value for gender is specified, but for some the value is missing.
The goal is to identify the missing ones based on other occurrences of the same name where the gender value is available. Either Excel or DAX solutions are appreciate.
A screenshot is attached for better clarification.

Imgur: The most awesome images on the Internet

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this...

E2 copied down
=IF(COUNTIFS($A$2:$A$12,D2,$B$2:$B$12,"?*"),LOOKUP(2,1/(($A$2:$A$12=D2)*(LEN($B$2:$B$12)>0)),$B$2:$B$12),"")

Hope this helps

M.

 

[Marcelo Branco]

Remark
Adjust the ranges in the formula above.
According the picture your data are in rows 2:26. So use A2:A26 and B2:B26

M.

 

[absherzad]

Thank you very much and it is working

Welcome to Mr Excel

Maybe this...

E2 copied down
=IF(COUNTIFS($A$2:$A$12,D2,$B$2:$B$12,"?*"),LOOKUP(2,1/(($A$2:$A$12=D2)*(LEN($B$2:$B$12)>0)),$B$2:$B$12),"")

Hope this helps

M.