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 an entire row in Excel and Google Sheets
Topic:Lookups (any direction)Excel & Google Sheets
=INDEX($B$2:$D$5,MATCH(A2,$A$2:$A$5,0),1)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| ID | Name | Dept | Salary |
| E01 | Alice | Finance | 70000 |
| E02 | Bob | HR | 55000 |
| E03 | Carlos | IT | 80000 |
| E04 | Diana | Finance | 72000 |
=INDEX($B$2:$D$5,MATCH(A2,$A$2:$A$5,0),1)→Alice
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
- 1Identify the lookup key column (e.g. A) and the data columns to return (e.g. B:D).
- 2Use MATCH to find the row number: MATCH(lookupValue,$A$2:$A$100,0).
- 3Use INDEX to retrieve each column: =INDEX($B$2:$D$100,MATCH(A2,$A$2:$A$100,0),1) for column B, change the last argument to 2 for column C, 3 for column D. In Excel 365, =XLOOKUP(A2,$A$2:$A$100,$B$2:$D$100) returns all columns at once.
Tips
- Copy the INDEX/MATCH formula across columns and change only the column index number, or use COLUMN()-offset to make it auto-increment as you drag right.
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
Can XLOOKUP return an entire row in one formula?
Yes. In Excel 365 and Google Sheets, =XLOOKUP(lookup_value,lookup_array,entire_row_range) spills all matched columns automatically into adjacent cells.
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.