# How to assign a group by the first letter of a name in Excel

List the first letter of each group and use an approximate match: `=XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1)`.

**Excel:** `=XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1)` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Last name | Line |  |  | From | Line |
| Okafor |  |  |  | A | Line 1 |
| Becker |  |  |  | G | Line 2 |
| Martinez |  |  |  | N | Line 3 |
| Zhang |  |  |  | T | Line 4 |
| Garcia |  |  |  |  |  |
| Nguyen |  |  |  |  |  |
| Thompson |  |  |  |  |  |
| Fischer |  |  |  |  |  |

Formula in B2, filled to B9: `=XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1)`

Result: Line 3; Line 1; Line 2; Line 4; Line 2; Line 3; Line 4; Line 1

## How it works

Text compares alphabetically, so an approximate match works on letters the way it does on numbers. LEFT takes the first letter, and match mode `-1` returns the group whose starting letter is the last one at or before it: O comes after N and before T, so Okafor goes to Line 3.

## Other ways

- `=VLOOKUP(A2,$E$2:$F$5,2,TRUE)`: An approximate VLOOKUP on the whole name gives the same groups: "Martinez" sorts after "G" and before "N".

## Common mistakes

- The table must start at "A", or names earlier in the alphabet return #N/A.
- Names with a leading space or an accented first letter (Ávila) may sort unexpectedly. TRIM the names first.

## Questions

**Is the comparison case-sensitive?**

No. "okafor" and "Okafor" land in the same group.

**How do I count how many people are in each line?**

Fill the line column, then use `=COUNTIF(B2:B9,"Line 1")`.

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/group-by-first-letter