I have a data set similar to the google sheet image below. I currently have a formula in column C that populates the first unique value in column B.

ARRAYFORMULA(INDEX(IF(B2:B="""",,IF(COUNTIFS(B2:B, B2:B, ROW(B2:B), ""<=""&ROW(B2:B))=1, 1, )))

However I need a formula to return what is in column D. This populates the cell for the first row that has both a unique 'Color' and 'Name'. For example, row 2 is populated for Purple - James and row 12 for White - James. But row 38 for Purple - James is not populated.

enter image description here

1 Answer 1


Use map() and filter(), like this:

=map(A2:A, B2:B, lambda(color, name, 
    rows(filter(B2:name, A2:color = color, B2:name = name)) = 1, 

If you just want to get a list of unique color-name pairs, use this:


See map(), filter() and unique().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.