I have a similar spreadsheet structured as the shared image. Column A has names listed, sometimes multiple times. I need to pick out those names where the adjacent value is Unavailable all times. I can’t figure out what function to use. Should I use Vlookup? Uniques?
So in this example. Only Spencer should fit the criteria.
Any help is appreciated
Thank you for your time.
>Solution :
Use FILTER() with COUNTIFS as the criteria. We use UNIQUE because duplicates would be returned
=UNIQUE(FILTER(A2:A9,COUNTIFS(A2:A9,A2:A9)=COUNTIFS(A2:A9,A2:A9,B2:B9,"Unavailable"),""))
This will spill a list, so make sure the cells below where you put the formula are empty.