Excel can return a value from a column to the left of the one you search, even though older lookup formulas are often taught to search only from left to right.
The right method depends on your Excel version and whether you want a simple formula or compatibility with older workbooks.
Use XLOOKUP for a left lookup
In Microsoft 365, Excel 2021, and newer versions, XLOOKUP is usually the most direct option.
It searches one range and returns the corresponding value from another range, regardless of which range is on the left.
Suppose employee IDs are in B2:B6 and employee names are in A2:A6.
To find the name associated with the ID in E2, enter:
=XLOOKUP(E2,B2:B6,A2:A6,"Not found")If E2 contains 1042, and that ID appears in B4 beside the name Rina Patel in A4, the formula returns Rina Patel.
The first range after the lookup value is the search range; the next range is what Excel returns from.
They can be in either column order.
The fourth argument, "Not found", is optional.
It lets you choose a clear result when no exact match exists instead of displaying #N/A.
For exact matches, the default XLOOKUP match mode is already exact.
Return the last matching value
If the same ID appears more than once and you want the last matching row, use XLOOKUP's search-mode argument:
=XLOOKUP(E2,B2:B6,A2:A6,"Not found",0,-1)The -1 at the end tells Excel to search from the last item to the first.
Keep the lookup and return ranges the same size and aligned to the same rows.
Use INDEX and MATCH in older Excel versions
For Excel versions without XLOOKUP, combine INDEX and MATCH.
MATCH finds the position of the lookup value, and INDEX returns the item at that position from the column to the left.
=INDEX(A2:A6,MATCH(E2,B2:B6,0))Here, MATCH(E2,B2:B6,0) searches for an exact match and returns its position within B2:B6.
If the ID is in the third cell of that range, MATCH returns 3; INDEX then returns the third cell of A2:A6.
With the example data, that is Rina Patel.
To show a message instead of an error when there is no match, wrap the formula in IFNA:
=IFNA(INDEX(A2:A6,MATCH(E2,B2:B6,0)),"Not found")INDEX and MATCH are available in many older Excel releases, including Excel 2016 and Excel 2019.
They also work in Microsoft 365 and Google Sheets, though function availability and separators can vary by spreadsheet locale.
Can VLOOKUP return a value to the left?
VLOOKUP normally searches the first column of its table range and returns a value from a column to its right.
It cannot directly return a column to the left of that first column.
One workaround is to use CHOOSE to build a virtual two-column table with the return column first.
Using the employee example, the return names are in A2:A6 and the IDs to search are in B2:B6:
=VLOOKUP(E2,CHOOSE({1,2},B2:B6,A2:A6),2,FALSE)CHOOSE({1,2},B2:B6,A2:A6) presents the ID range as the first column and the name range as the second.
VLOOKUP can then search that constructed table and return column 2.
The final argument, FALSE, requests an exact match.
This workaround is more complex than XLOOKUP or INDEX and MATCH.
In some older Excel versions, entering the formula may require confirming it as an array formula with Ctrl+Shift+Enter; newer dynamic-array versions handle it differently.
For a workbook shared across versions, test the formula in the oldest version your audience uses.
Which formula should you choose?
- Use XLOOKUP in Microsoft 365 or Excel 2021 and later for a readable formula with a built-in not-found result.
- Use INDEX and MATCH when you need a dependable exact-match lookup in older Excel versions.
- Use VLOOKUP with CHOOSE when a workbook specifically needs VLOOKUP or you are adapting an existing VLOOKUP-based model.
Google Sheets supports XLOOKUP and INDEX with MATCH.
If a formula is rejected because of separators, your regional settings may require semicolons instead of commas, for example =XLOOKUP(E2;B2:B6;A2:A6;"Not found").
Common problems with left lookups
Why does the formula return #N/A?
An exact-match formula returns #N/A when it cannot find the lookup value.
Check that the value exists in the search range and that the values have compatible types.
A number stored as text, such as "1042", may not match the numeric value 1042.
Extra spaces can also prevent a text match; clean imported data with functions such as TRIM when appropriate.
Why does the formula return the wrong row?
Make sure the search and return ranges begin and end on corresponding rows.
For example, B2:B6 should pair with A2:A6, not A3:A7.
Also check for duplicate lookup values: XLOOKUP and MATCH return the first match by default, so duplicate IDs may produce a result from an earlier row.
Can a lookup return multiple columns?
XLOOKUP can return a range of adjacent columns when the return array includes them.
For example, =XLOOKUP(E2,B2:B6,A2:C6,"Not found") returns the matching row from columns A through C in versions that support dynamic arrays.
The result spills into neighboring cells, so those cells must be empty.
INDEX and MATCH can also be adapted to return multiple columns, but the formula depends on how the output is arranged.
Practical checks before using the result
- Use an exact-match setting when IDs, names, or codes must match exactly.
- Keep lookup and return ranges aligned and the same height.
- Decide how the formula should handle missing values, using XLOOKUP's not-found argument or IFNA.
- Check whether duplicate keys are expected and whether the first or last match is appropriate.
- Confirm that the target Excel version supports the functions and array behavior in the formula.