How to get unique combinations of two columns in Excel
Formula (Excel and Google Sheets)
=UNIQUE(CHOOSECOLS(A2:D12,1,3))Pick the two columns with CHOOSECOLS and pass them to UNIQUE: =UNIQUE(CHOOSECOLS(A2:D12,1,3)) lists each region and product pair once.
- Result on the example
- a 7-row list that spills from F2 in
F2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
CHOOSECOLS builds a two-column array from columns 1 and 3 of the table, region and product. UNIQUE then compares each pair as a whole, so East with Widget and East with Gizmo are different combinations, while the second East with Widget row is dropped.
=UNIQUE(CHOOSECOLS(A2:D12,1,3))Result{7 rows × 2 cols} "East", "Widget", "West", "Gadget", …
A2:D1211 rows × 4 cols: "East", "Ana", "Widget", 40, …A range: 11 rows × 4 columns.
CHOOSECOLS(A2:D12,1,3){11 rows × 2 cols} "East", "Widget", "West", "Gadget", …CHOOSECOLS: The columns you name from an array, in the order you name them.- array
A2:D1211 rows × 4 cols: "East", "Ana", "Widget", 40, … - col_num1
1 - col_num2
3
- array
UNIQUE(CHOOSECOLS(A2:D12,1,3)){7 rows × 2 cols} "East", "Widget", "West", "Gadget", …UNIQUE: Distinct rows of a range. Spills.- array
CHOOSECOLS(A2:D12,1,3){11 rows × 2 cols} "East", "Widget", "West", "Gadget", …
- array
In Google Sheets
The same formula works in Google Sheets: =UNIQUE(CHOOSECOLS(A2:D12,1,3)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 7 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =UNIQUE(HSTACK(A2:A12,C2:C12)) | spills 7 × 2 | HSTACK puts the two columns side by side; same result. Use it when the columns are in different tables. |
| =SORT(UNIQUE(CHOOSECOLS(A2:D12,1,3)),{1,2})Excel only | spills 7 × 2Different result | Sort by region, then product. Sorted by region, then by product. |
Common mistakes
=UNIQUE(A2:A12)&UNIQUE(C2:C12)doesn't work: the two unique lists have different lengths and don't line up row by row.- If the columns are next to each other, you don't need CHOOSECOLS:
=UNIQUE(A2:B12).
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get unique combinations of two columns
F2, list each combination of region (column A) and product (column C) that appears in the data, once each.Questions
How do I count how many times each combination appears?
Add COUNTIFS on both columns: =LET(u,UNIQUE(CHOOSECOLS(A2:D12,1,3)),HSTACK(u,COUNTIFS(A2:A12,CHOOSECOLS(u,1),C2:C12,CHOOSECOLS(u,2)))).
How do I count the number of combinations?
Count the rows: =ROWS(UNIQUE(CHOOSECOLS(A2:D12,1,3))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.