How to VLOOKUP to the Left in Google Sheets
=XLOOKUP(key, search_range, result_range), =INDEX(result_range, MATCH(key, search_range, 0)), or =VLOOKUP(key, {search_range, result_range}, 2, FALSE).
Why VLOOKUP cannot look left
VLOOKUP searches the first column of the range you give it, then returns a value from a column counted to the right of that. Google's help page says it plainly: VLOOKUP only looks to the search key column (index 1) or columns that are further right. If the value you want sits in a column to the left of the key, no index number can reach it.
The example
Here is a product table in cells A1:C5. The product code is in column B, but you want to find the name, which is in column A, to its left.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Code | Price |
| 2 | Pen | K-101 | 1.5 |
| 3 | Notebook | K-102 | 4.25 |
| 4 | Stapler | K-103 | 9 |
| 5 | Tape | K-104 | 2.75 |
Way 1: XLOOKUP
XLOOKUP takes a search range and a separate result range, so their order on the sheet does not matter. The XLOOKUP help page notes that the match range can sit to the right of the result range.
=XLOOKUP("K-102", B2:B5, A2:A5)
This returns Notebook. It is the shortest and clearest option. See the XLOOKUP guide for the details.
Way 2: INDEX and MATCH
MATCH finds the position of the code in column B, and INDEX returns the item at that position in column A.
=INDEX(A2:A5, MATCH("K-102", B2:B5, 0))
This also returns Notebook. The final 0 asks for an exact match. The INDEX and MATCH guide explains why it matters.
Way 3: VLOOKUP with an array
If you want to keep VLOOKUP, build a new two-column table in the order you need. Curly braces make an array, and a comma inside them places ranges side by side. Put the search column first and the result column second:
=VLOOKUP("K-104", {B2:B5, A2:A5}, 2, FALSE)
This returns Tape. In the array, column B becomes the first column and column A the second, so index 2 now points to the names. Use FALSE for an exact match.
Which one should you use?
- XLOOKUP when it is available. It is the easiest to read and to maintain.
- INDEX and MATCH when you share a formula with people who know it, or need the position as well.
- The array trick when you only want to adjust an existing VLOOKUP.
Moving the columns so the key comes first also works, but it changes your data layout, so a formula is usually the safer choice.
Common mistakes
- Leaving out FALSE. VLOOKUP's default is an approximate match, which gives wrong answers on unsorted data.
- Ranges of different sizes. In XLOOKUP and INDEX, the search and result ranges should have the same number of rows.
- Keeping the old index. With the array trick, the index refers to the position in the array, not the sheet.
Try it yourself
Use the product table above (A1:C5). Type your formula into the box, starting with =, then press Check.
Exercise 1: XLOOKUP to the left
Write an XLOOKUP that returns the Name (column A) for the code K-102 (column B).
Exercise 2: VLOOKUP with an array
Write a VLOOKUP that returns the Name for the code K-104, using an array that puts column B before column A. Use an exact match.
What to learn next
- XLOOKUP vs VLOOKUP: which lookup function to use and why.
- VLOOKUP #N/A error: the causes and the fixes.
- FILTER: pull out the rows that match.
Syntax and rules in this guide were checked against Google's VLOOKUP to the left help page. See all guides.