How to lookup and create a unique list from table in Excel?
In daily data processing using Excel, it is common to come across tables that contain duplicate information. For example, you might have a table listing classes alongside the names of students, where some students’ names appear multiple times under the same class. Suppose you need to extract a list of unique names for “Class A” only, as shown in the screenshot below. What is the best way to achieve this? This guide explores several practical methods, starting with a VLOOKUP-based solution.
- Lookup and create a unique list with formula
- Use the UNIQUE function to get filtered unique values (Excel 365, Excel 2021 or later)
- Other Built-in Excel Methods - Use Remove Duplicates with Filtering
Lookup and create a unique list with formula
To apply this method, first select the cell just below your criterion — for instance, if your criterion cell (“Class A”) is in D1, select D2. Then, input the following array formula:
=IFERROR(INDEX(B$1:B$13, MATCH(0, COUNTIF(D$1:D1, IF(A$1:A$13=D$1,B$1:B$13,D$1)),0)),"")After entering the formula, instead of just pressing Enter, you must press Ctrl + Shift + Enter together to complete it as an array formula. Once the first unique value appears, fill the formula down by dragging the fill handle until you reach a blank cell, indicating there are no more unique names to display.
Notes and Tips:
- B$1:B$13: The column that contains the names you wish to extract.
- A$1:A$13: The column that contains your filtering criteria (e.g., class names).
- D$1: The cell that holds your filter value, such as “Class A”.
- This formula requires entering as an array formula (Ctrl + Shift + Enter). In Excel 365 and later versions, array formulas may behave dynamically with just Enter, but for compatibility with older versions, always use the array formula shortcut.
- If your data range changes, adjust the references accordingly to avoid errors.
This method is suitable for users who need compatibility with older Excel versions. However, the formula is somewhat complex to set up and maintain, especially for new Excel users.
For easier solutions or to handle large amounts of data with greater efficiency, consider the methods below as well.
Excel Formula - Use the UNIQUE function to get filtered unique values
If you are using Excel 365, Excel 2021, or later, you can take advantage of new dynamic array functions to effortlessly generate a unique, instantly updating list based on criteria. Here, the UNIQUE and FILTER functions are used together to extract unique names for “Class A”.
- Enter the following formula in the target cell (for example, in D2):
=UNIQUE(FILTER(B2:B13, A2:A13="Class A")) - Press Enter, and the unique names matching “Class A” are listed automatically below the formula cell. The list will automatically expand or shrink as your data changes.
- In the above formula:
- B2:B13: The range of student names.
- A2:A13: The range containing class names.
- You can replace "Class A" with another criterion as needed. If you reference a cell (e.g., E1), use
="Class A"or=E1appropriately in the formula. - This function is dynamic — if you update your table, the unique list is refreshed automatically.
Advantages: Automatically updates with your source data, very easy to set up, ideal for regularly changing tables.
Limitations: Only available in Excel 365, Excel 2021 and later versions; not supported in older Excel versions.
Other Built-in Excel Methods - Use Remove Duplicates with Filtering
In addition to formula-based approaches, you can take advantage of Excel’s built-in Remove Duplicates feature combined with filtering to generate a unique list for a specified category. This method is intuitive and does not require writing any formulas.
Here’s how you can use this method to extract unique student names under “Class A”:
- Filter your data for “Class A”:
Select your data range (e.g., A1:B13). Go to the Data tab and click Filter. Use the filter dropdown in the “Class” column to show only rows where the class is “Class A”. - Copy the filtered results:
Select only the student names displayed after filtering. Copy this selection (Ctrl+C) and paste it into another column or worksheet. - Remove duplicates:
With the pasted unique range selected, under the Data tab, click Remove Duplicates. In the dialog that appears, ensure only the relevant column (“Student Name”) is checked, then confirm.
This will leave you with a list of unique student names corresponding to “Class A”.
- Advantages: No formula knowledge required, fast and straightforward; suitable for one-off data extraction or reporting.
- Limitations: The process is manual and requires re-doing each time your data changes. Not suitable for automatically updating reports.
By understanding each method and choosing the one appropriate for your Excel version and workflow, you can efficiently create unique lists filtered by specific criteria. If you encounter formula errors or issues during operation, double-check the cell references, ensure all data is in a contiguous range, and confirm you are using a version of Excel that supports the necessary functions. For batch processing or repetitive tasks, leveraging built-in features like Remove Duplicates or dynamic formulas can save significant time compared to manual methods.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in