Formula Help

Copper Contributor

Hi! 

I am updating a template that the company uses. 

The current template looks at the data set and returns 2 unique employee name that worked for that specific day. I need to update the template so it can return the 3 unique employee names that are associated with that date. Below are the formulas currently used to return the 2 names:

1st name =VLOOKUP(Summary!A3, Data!A:J,2, 0)

2nd name =LOOKUP(2,1/(Data!A:A=Summary!A3), Data!B:B)

 

I have tried to put like =LOOKUP(3,1/(Data!A:A=Summary!A3), Data!B:B) but since the names are listed multiple times for the same date set it will not return the unique names. I have attached a picture of the data and the ideal state. 

 

TIA

Data.png

Ideal State.png

3 Replies

@cheherr 

If you have Microsoft 365 or Office 2021, you can use a single formula to return all names:

 

=FILTER(Data!B:B, Data!A:A=A3, "")

I do have Microsoft 365 but for some reason that formula does not work :(

@cheherr 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?