Skip to content
How do I write an E...
 
Notifications
Clear all

How do I write an Excel array formula to extract unique items from a list if a condition is met in a column next to the list?


admin
Posts: 49
Admin
Topic starter
(@admin)
Member
Joined: 8 months ago

How do I write an Excel array formula to extract unique items from a list if a condition is met in a column next to the list?

If you have Microsoft 365 or Excel 2021, you have the FILTER and UNIQUE functions. To return unique items from column B when column D is greater than 5, you might use:

  • =UNIQUE(FILTER(B2D100>5)) 

If you have an older version of Excel, you may use INDEX, AGGREGATE & MATCH (requires Excel 2010 or later) in a rather ungainly formula. You might need to array-enter this formula (Control + Shift + Enter).

  • =IFERROR(INDEX(B$2B$100)-ROW(B$2)+1)/((ROW(B$2K2))),"") 

If you are stuck with Excel 2007, God bless. But you can use a vaguely similar array-entered formula using LARGE instead of AGGREGATE. Note that it returns the unique items from the bottom of the list first.

  • =IFERROR(INDEX(B$2K2))),"") 
 
Topic Tags
Share: