MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: One Hit Wonders with UNIQUE


September 28, 2020 - by Bill Jelen

Excel One Hit Wonders with UNIQUE. Photo Credit: Eric Nopanen at Unsplash.com

For me, I can't imagine why I would ever need a list of items that have been sold exactly once. My only example is the One-Hit Wonders segment on Casey Kasem's American Top 40 radio show.

To get a list of artists who had exactly one hit, use =UNIQUE(B4:B6132..True). In the figure below, the UNIQUE function is wrapped in a SORT function so the resulting list is alphabetical.


By using TRUE for the Occurs_Once argument in UNIQUE, you can find the artists who only appeared once in the original database. Sort those alphabetically using =SORT(UNIQUE(B4:B6132,False,True)).

To get the titles in column J, a VLOOKUP uses an array as the first argument. This is pretty wild - one VLOOKUP formula is actually doing over 1000 lookups and returning all 1000 results.

To get the title next to the artist, use =VLOOKUP(I5#,B4:C6132,2,False). You know that these one-hit-wonder artists only appear once in the database, so VLOOKUP works.

Another approach is to use a FILTER function combined with IFERROR and MATCH.

Use =FILTER(A4:D6132,IFERROR(MATCH(B4:B6132,UNIQUE(B4:B6132,False,True),0),False)) to return all four columns from the table.

Title Photo: Eric Nopanen at Unsplash.com


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.