# How to return multiple matches across a row in Excel

Filter the matches and turn the list sideways: `=TRANSPOSE(FILTER(B2:B11,A2:A11=E1))` spills them across the row.

**Excel:** `=TRANSPOSE(FILTER(B2:B11,A2:A11=E1))` (Excel 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Student | Course |  | Student | Jamal Wright |
| Ava Lindqvist | Biology |  | Courses |  |
| Jamal Wright | Calculus |  |  |  |
| Ava Lindqvist | Spanish II |  |  |  |
| Jamal Wright | Physics |  |  |  |
| Nina Petrov | Art History |  |  |  |
| Ava Lindqvist | Chemistry |  |  |  |
| Jamal Wright | Economics |  |  |  |
| Nina Petrov | Calculus |  |  |  |
| Ava Lindqvist | World History |  |  |  |
| Jamal Wright | Spanish II |  |  |  |

Formula in E2: `=TRANSPOSE(FILTER(B2:B11,A2:A11=E1))`

Result (spills 1 × 4): Calculus, Physics, Economics, Spanish II

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

## Other ways

- `=TOROW(FILTER(B2:B11,A2:A11=E1))`: 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))`: 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.

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

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/return-matches-across-a-row