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 the nth matching value in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=IFERROR(INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6="Alice",ROW($A$2:$A$6)-ROW($A$2)+1),2)),"")✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Name | Task |
| Alice | Design |
| Bob | Dev |
| Alice | Review |
| Alice | Testing |
=IFERROR(INDEX($B$2:$B$6,SMALL(IF($A$2:$A$6="Alice",ROW($A$2:$A$6)-ROW($A$2)+1),2)),"")→
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
- 1Build the IF array that returns the relative row numbers of matches: IF($A$2:$A$100="Alice",ROW($A$2:$A$100)-ROW($A$2)+1) produces row numbers where Alice appears, FALSE elsewhere.
- 2Use SMALL(...,n) to extract the nth smallest row number from that array — SMALL(...,2) gives the 2nd match's row.
- 3Wrap in INDEX to retrieve the value: =INDEX($B$2:$B$100,SMALL(IF(...),2)). In Excel 2019 confirm with Ctrl+Shift+Enter; wrap in IFERROR to suppress errors when fewer than n matches exist.
Tips
- In Excel 365 and Google Sheets you can use FILTER to get all matches at once: =FILTER(B2:B100,A2:A100="Alice") then reference the result by row, which is cleaner than the SMALL/IF pattern.
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
How do I look up the last matching value instead of the nth?
Use LARGE instead of SMALL with 1 as the k argument: =INDEX($B$2:$B$100,LARGE(IF($A$2:$A$100="Alice",ROW($A$2:$A$100)-ROW($A$2)+1),1)) returns the last match.
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.