# How to get unique combinations of two columns in Excel

Pick the two columns with CHOOSECOLS and pass them to UNIQUE: `=UNIQUE(CHOOSECOLS(A2:D12,1,3))` lists each region and product pair once.

**Excel:** `=UNIQUE(CHOOSECOLS(A2:D12,1,3))` (Excel 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Region | Rep | Product | Units |  | Region | Product |
| East | Ana | Widget | 40 |  |  |  |
| West | Raj | Gadget | 25 |  |  |  |
| East | Lee | Gizmo | 12 |  |  |  |
| East | Ana | Widget | 18 |  |  |  |
| North | Kim | Gadget | 30 |  |  |  |
| West | Mo | Gadget | 22 |  |  |  |
| West | Raj | Widget | 35 |  |  |  |
| East | Lee | Gizmo | 9 |  |  |  |
| North | Kim | Widget | 16 |  |  |  |
| North | Sam | Gadget | 28 |  |  |  |
| East | Ana | Gadget | 14 |  |  |  |

Formula in F2: `=UNIQUE(CHOOSECOLS(A2:D12,1,3))`

Result (spills 7 × 2): East, Widget; West, Gadget; East, Gizmo; North, Gadget; West, Widget; North, Widget; East, Gadget

## 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.

## Other ways

- `=UNIQUE(HSTACK(A2:A12,C2:C12))`: 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})`: 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)`.

## 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)))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/unique-combinations-of-two-columns