Return a range in Column 2 based on the look-up results in Column 1-CodePudding

I am trying to achieve this task in Excel. In column 1, I look up each letter, and return the values (yes or no) in column 2. If there is one value is yes, then it is yes for that that letter, otherwise it is no.

The second part is fairly easy. Once I have the range of cells for each letter, then I can use COUNTIF and IF. However, how to do return a range of cells in column 2 based on column 1? Match or lookup functions stop when they find the first match, but I need the function to go through all the values in Column 1 and return all the matches in Column 2.

A Yes

A No

B Yes

B No

C No

This can also be multiple values for example

A Yes

A No

A Unknown

I expect to get a table that is

A Yes

B Yes

C No

CodePudding user response：

If I understand correctly, something like:

=UNIQUE(A1:A7)

to return the unique values from column 1, and

=IF(COUNTIFS(A1:A7,D1#,B1:B7,"Yes")>0,"Yes","No")

to return Yes or No.