How to Combine UNIQUE and Lookup Formulas in Excel
Question details
Create an Excel formula that extracts unique values and combines them with lookup results based on specific matching criteria.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- The user needs to extract a list of distinct items from a dataset and use a lookup formula to find or aggregate the corresponding data for those distinct items.
- Observed behavior
- The user is looking for the correct combination of modern functions (like UNIQUE, XLOOKUP, FILTER, and TEXTJOIN) to achieve the desired output layout.
Ensure you are using a modern version of Excel (Microsoft 365 or 2021) or the latest version of WPS Office, as the UNIQUE, FILTER, and XLOOKUP dynamic array functions are not supported in older versions.
Combine UNIQUE and XLOOKUP for the First Match
Use this method to extract a list of distinct items and find the first corresponding value for each item.
This combination leverages dynamic arrays. The UNIQUE function extracts the distinct items, and XLOOKUP uses the spilled array operator (#) to automatically look up data for every unique item.
In a new cell (e.g., D2), type =UNIQUE(A2:A10) to generate a list of unique items from your source column.
In the adjacent cell (e.g., E2), type =XLOOKUP(D2#, A2:A10, B2:B10). The '#' sign ensures the lookup formula spills automatically alongside the unique array.
Press Enter. The formula will return the corresponding first match from column B for every unique value generated in column D.
Combine UNIQUE, FILTER, and TEXTJOIN for Multiple Matches
Use this method if you need to return all matching results for a unique value, combined into a single cell and separated by commas.
Master Dynamic Array Formulas in WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions, including UNIQUE, FILTER, and XLOOKUP, allowing you to build powerful data reports with ease. You can seamlessly implement these advanced lookup combinations without worrying about compatibility issues.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your dataset.
- 2. Input the UNIQUE formula: Select the cell where you want the unique list to begin and enter your =UNIQUE() formula to extract distinct values.
- 3. Apply the lookup function: Use lookup functions like =XLOOKUP() referencing the spilled array cell (e.g., D2#) to retrieve your results.
Frequently Asked Questions
Why does my UNIQUE or XLOOKUP formula return a #NAME? error?
This usually occurs if you are using an older version of Excel (like Excel 2016 or 2019) that does not support dynamic array functions. Try updating your software or use WPS Office, which fully supports these modern functions.
What does the hashtag (#) mean in a formula like D2#?
The hashtag is the spilled range operator. It tells the formula to dynamically reference the entire array returned by the formula starting in cell D2, rather than just the single cell. As the unique list grows or shrinks, the referenced range adjusts automatically.
Can I use VLOOKUP instead of XLOOKUP with the UNIQUE function?
Yes, you can use =VLOOKUP(D2#, A2:C10, 2, FALSE). However, XLOOKUP is generally recommended because it is more flexible, handles errors better, and does not require the lookup column to be on the far left of the array.
How do I filter out blanks when using the UNIQUE formula?
You can nest the FILTER function inside the UNIQUE function. For example, use =UNIQUE(FILTER(A2:A10, A2:A10<>"")) to extract unique values while ignoring any empty cells in the range.
