How to look up a value to the left in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(G1,D2:D7,A2:A7)Use XLOOKUP, which can return any column: =XLOOKUP(value, lookup_column, result_column). In older versions use INDEX and MATCH.
- Result on the example
- E-2203 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
XLOOKUP takes the column to search and the column to return as separate ranges, so the result can sit to the left of the search column. Here it finds the email in D2:D7 and returns the ID from the same row of A2:A7.
=XLOOKUP(G1,D2:D7,A2:A7)Result"E-2203"
G1"noor.haddad@corp.example"The value in G1.
D2:D76 rows × 1 col: "lena.park@corp.example", "omar.ruiz@corp.example", "noor.haddad@corp.example", "tom.becker@corp.example", …A range: 6 cells in one column.
A2:A76 rows × 1 col: "E-2201", "E-2202", "E-2203", "E-2204", …A range: 6 cells in one column.
XLOOKUP(G1,D2:D7,A2:A7)"E-2203"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
G1"noor.haddad@corp.example" - lookup_array
D2:D76 rows × 1 col: "lena.park@corp.example", "omar.ruiz@corp.example", "noor.haddad@corp.example", "tom.becker@corp.example", … - return_array
A2:A76 rows × 1 col: "E-2201", "E-2202", "E-2203", "E-2204", …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(G1,D2:D7,A2:A7). We ran it in Google Sheets mode on the same data and it returns E-2203.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A7,MATCH(G1,D2:D7,0)) | E-2203 | Works in every Excel version: MATCH finds the row number, INDEX returns that row from column A. |
Common mistakes
- VLOOKUP can't do this: it only returns columns to the right of the first column of its table.
- The two ranges must have the same number of rows, or XLOOKUP returns #VALUE!.
- MATCH needs 0 as its third argument for an exact match.
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.
Look up a value to the left
G2, return the employee ID (column A) for the email address in G1.Questions
Is INDEX/MATCH faster than XLOOKUP?
On ordinary sheets you won't notice a difference. Choose XLOOKUP for readability if everyone has Excel 2021 or later.
How do I return several columns at once?
Give XLOOKUP a wider return range, like =XLOOKUP(G1,D2:D7,A2:C7); the result spills across.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.