How to return multiple matches across a row in Excel
Formula (Excel and Google Sheets)
=TRANSPOSE(FILTER(B2:B11,A2:A11=E1))Filter the matches and turn the list sideways: =TRANSPOSE(FILTER(B2:B11,A2:A11=E1)) spills them across the row.
- Result on the example
- a 1-row list that spills from E2 in
E2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER returns the courses on the rows where the student matches, as a vertical list. TRANSPOSE turns that list into a row, so the four courses spill from E2 to the right. Add a course for Jamal and the row grows by itself.
=TRANSPOSE(FILTER(B2:B11,A2:A11=E1))Result{1 row × 4 cols} "Calculus", "Physics", "Economics", "Spanish II"
B2:B1110 rows × 1 col: "Biology", "Calculus", "Spanish II", "Physics", …A range: 10 cells in one column.
A2:A1110 rows × 1 col: "Ava Lindqvist", "Jamal Wright", "Ava Lindqvist", "Jamal Wright", …A range: 10 cells in one column.
E1"Jamal Wright"The value in E1.
A2:A11=E1{10 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(B2:B11,A2:A11=E1){4 rows × 1 col} "Calculus", "Physics", "Economics", "Spanish II"FILTER: Rows of a range that meet every condition. #N/A when none match.- range
B2:B1110 rows × 1 col: "Biology", "Calculus", "Spanish II", "Physics", … - condition1
A2:A11=E1{10 rows × 1 col} FALSE, TRUE, FALSE, TRUE, …
- range
TRANSPOSE(FILTER(B2:B11,A2:A11=E1)){1 row × 4 cols} "Calculus", "Physics", "Economics", "Spanish II"TRANSPOSE: Flips rows and columns.- array
FILTER(B2:B11,A2:A11=E1){4 rows × 1 col} "Calculus", "Physics", "Economics", "Spanish II"
- array
In Google Sheets
The same formula works in Google Sheets: =TRANSPOSE(FILTER(B2:B11,A2:A11=E1)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 1 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TOROW(FILTER(B2:B11,A2:A11=E1)) | spills 1 × 4 | TOROW does the same flip and can also skip blanks and errors. Excel 2024, Microsoft 365 and Google Sheets. |
| =INDEX(B2:B11,SMALL(IF(A2:A11=E1,ROW(A2:A11)-ROW(A2)+1),1)) | CalculusDifferent result | For Excel 2019 and earlier: replace the 1 with COLUMNS($E$2:E2), fill right, and wrap in IFERROR to blank the extra cells. One course per cell: the first one here. |
Common mistakes
- The cells to the right must be empty, or you get #SPILL!.
- If the student has no courses, FILTER returns #CALC!. Add a third argument in Excel,
FILTER(…,"None"), or wrap the formula in IFERROR in Google Sheets.
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.
Return multiple matches across a row
E2, list every course of the student in E1 across the row, one course per cell.Questions
How do I list the matches in one cell instead?
Join them: =TEXTJOIN(", ",TRUE,FILTER(B2:B11,A2:A11=E1)).
How do I fill this for a list of students in a column?
Write it once per student row with a relative reference, like =TRANSPOSE(FILTER($B$2:$B$11,$A$2:$A$11=D5)), and fill down. Each row spills to the right.
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.