Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to look up and return multiple values in Excel and Google Sheets
Topic:Lookups (any direction)Heads up: Excel and Google Sheets do this differently.
Excel
=IFERROR(INDEX(B2:B6,SMALL(IF(A2:A6="Alpha",ROW(A2:A6)-ROW(A2)+1),ROW()-ROW(C2)+1)),"")Google Sheets
=FILTER(B2:B6,A2:A6="Alpha")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Category | Item |
| Alpha | Apple |
| Beta | Banana |
| Alpha | Avocado |
| Gamma | Grape |
| Alpha | Apricot |
=IFERROR(INDEX(B2:B6,SMALL(IF(A2:A6="Alpha",ROW(A2:A6)-ROW(A2)+1),ROW()-ROW(C2)+1)),"")→
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1In Google Sheets, enter =FILTER(B2:B6,A2:A6="Alpha") in an empty cell; results spill downward automatically.
- 2In Excel 365 use =FILTER(B2:B6,A2:A6="Alpha") — it works identically and also spills.
- 3In older Excel, enter the array formula =IFERROR(INDEX(B2:B6,SMALL(IF(A2:A6="Alpha",ROW(A2:A6)-ROW(A2)+1),ROW()-ROW(C2)+1)),"") with Ctrl+Shift+Enter, then copy it down as many rows as you expect matches.
- 4Replace "Alpha" with a cell reference (e.g. E2) to make the lookup value dynamic.
Tips
- In Excel 365 / Google Sheets, chain FILTER with SORT to return matches in a specific order: =SORT(FILTER(B2:B6,A2:A6="Alpha")).
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
What happens when there are no matches?
Wrap FILTER in IFERROR to display a custom message: =IFERROR(FILTER(B2:B6,A2:A6="X"),"No results").
Can I return matches from multiple columns?
Yes — expand the return range to include all desired columns, e.g. =FILTER(A2:B6,A2:A6="Alpha").
More on Lookups (any direction)
See all →Reference
HLOOKUP
Look up a value in the top row of a table and return a value from a row below it — the horizontal cousin of VLOOKUP.
How-toHow to look up the last matching value
Use LOOKUP with a search trick or XLOOKUP with search mode -1 to find the last occurrence of a value in a list.
How-toHow to do a lookup with wildcards
Use VLOOKUP or MATCH with wildcard characters (* and ?) to perform partial-match lookups on text values.
How-toHow to do a case-sensitive lookup
Use INDEX with MATCH and the EXACT function to perform a lookup that distinguishes between uppercase and lowercase text.
How-toHow to look up a value and sum the matches
Use SUMIF or SUMIFS to sum all values that match a lookup criterion, returning the total of all matching rows rather than a single result.
How-toHow to find the closest matching value
Use INDEX and MATCH with ABS and MIN to find the value in a list that is numerically closest to a target number.