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

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

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

Formula (Excel and Google Sheets)

=XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1)

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

Result on the example: one result per row in B2, filled down to B9
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in B2, filled down to B9. Change any input and the result updates, or edit the formula to experiment.

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.

=XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1)

Result"Line 3"

A2"Okafor"
The value in A2.
LEFT(A2)"O"
LEFT: Characters from the start of text.
- textA2"Okafor"
$E$2:$E$54 rows × 1 col: "A", "G", "N", "T"
A range: 4 cells in one column.
$F$2:$F$54 rows × 1 col: "Line 1", "Line 2", "Line 3", "Line 4"
A range: 4 cells in one column.
-1-1
Makes the value negative.
XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1)"Line 3"
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueLEFT(A2)"O"
- lookup_array$E$2:$E$54 rows × 1 col: "A", "G", "N", "T"
- return_array$F$2:$F$54 rows × 1 col: "Line 1", "Line 2", "Line 3", "Line 4"
- if_not_found(omitted)
- match_mode-1-1

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(LEFT(A2),$E$2:$E$5,$F$2:$F$5,,-1). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=VLOOKUP(A2,$E$2:$F$5,2,TRUE)	Line 3, Line 1, Line 2…	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.

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.

Assign a group by the first letter of a name

Check-in lines are split by last name: A to F, G to M, N to S, T to Z. In B2, return the line for the name in A2, then fill down to B9.

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").

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.