How to Return One Unique Median for Each Code in Excel
Question details
The user needs to list unique codes from a dataset and retrieve the corresponding median value for each code on a separate worksheet.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Organizing data across worksheets where a distinct list of identifier codes is required alongside their pre-calculated median values, avoiding manual copying and pasting.
- Observed behavior
- The goal is to automatically generate a deduplicated list of codes in a new worksheet and match each unique code with its exact median value from the source worksheet.
Ensure you are using a version of your spreadsheet software (such as Microsoft 365 or the latest WPS Office) that supports Dynamic Array formulas, specifically the UNIQUE and XLOOKUP functions.
Use UNIQUE and XLOOKUP Formulas Across Worksheets
This is the most robust method to extract a clean list of unique codes and accurately pull their corresponding median values from another sheet.
This approach uses two separate formulas. The UNIQUE function automatically filters out duplicates to give you a clean list of codes. Then, the XLOOKUP function maps those unique codes back to your original dataset to grab the correct median value.
Go to the destination worksheet (e.g., Sheet2) where you want the results to appear, and click on the first cell of your target column (like A1).
Type the formula =UNIQUE(Sheet1!A:A) and press Enter. This will spill a list of all distinct codes found in Column A of Sheet1.
Click on the adjacent cell (e.g., B1) next to your first unique code. Enter the formula =XLOOKUP(A1,Sheet1!A:A,Sheet1!B:B) and press Enter.
Select cell B1, click and hold the small square at the bottom-right corner of the cell (fill handle), and drag it down to apply the XLOOKUP formula for all the unique codes in column A.
Extract Unique Code and Median Pairs Directly
If every code strictly corresponds to only one specific median value, you can extract both columns simultaneously with a single formula.
Calculate and Extract Medians Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like UNIQUE and XLOOKUP, making it incredibly simple to analyze data, extract unique records, and match values across different sheets.
- 1. Open Your Data: Launch WPS Spreadsheet and open your existing dataset containing the codes and medians.
- 2. Use the UNIQUE Function: Navigate to a blank worksheet and type =UNIQUE(Sheet1!A:A) in cell A1 to generate your list of distinct codes.
- 3. Fetch the Medians: In cell B1, type =XLOOKUP(A1,Sheet1!A:A,Sheet1!B:B) to retrieve the corresponding median.
- 4. Auto-Fill Down: Double-click the fill handle in cell B1 to instantly apply the lookup formula to your entire list of unique codes.
Frequently Asked Questions
What if I am using an older version of Excel that doesn't support UNIQUE?
If your spreadsheet software does not support Dynamic Arrays, you can first copy your code column to the destination sheet, use the 'Remove Duplicates' feature located under the Data tab, and then use VLOOKUP or INDEX/MATCH to pull the median values.
Why is my XLOOKUP returning an #N/A error?
This error occurs if the specific code in your destination sheet cannot be found in the source array. Ensure there are no hidden trailing spaces in your source data and that your lookup ranges accurately span the data you intend to match.
Can I calculate the median dynamically if I only have raw data?
Yes. If your source sheet has raw numbers rather than pre-calculated medians, you can calculate the median for each unique code dynamically by using the FILTER function wrapped in a MEDIAN function, like =MEDIAN(FILTER(Sheet1!B:B, Sheet1!A:A=A1)).
