How To Create A Unique List From A Large Data Range Based On A Single Criteria In Excel

In this tutorial we’ll take a look at how to use an array formula utilizing INDEX, IF, SMALL and ROW functions to extract unique values from a large list into a table based on a specific criteria.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have list of 4 names in column E along with their associated scored in column F:


In I1 I have a Data Validation drop down list with the 4 names. When I select a name, only that name with the associated scores appear in H3:I9:


Notice also that when there are only two scores, as with Tom, only those two lines of data appear:


The formulas for columns H & I are, respectively:


Note that these are array formulas so they need to be entered with Ctrl + Shift + Enter. The only difference between the two formulas is which column they are pulling the data from, as highlighted.

This is quite a unique process, so take just some time and watch the seven minute video to best understand how the process works.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

No comments yet.

Leave a Reply