How to VLOOKUP to the Left in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Intermediate · 7 min read · includes 2 free exercises

In short: VLOOKUP only returns values from the search column or columns to its right. To look left, use =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.

ABC
1NameCodePrice
2PenK-1011.5
3NotebookK-1024.25
4StaplerK-1039
5TapeK-1042.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?

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

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).

Hint: search B2:B5, return A2:A5.

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.

Hint: the table argument is {B2:B5, A2:A5}, and the index is 2.

What to learn next

Syntax and rules in this guide were checked against Google's VLOOKUP to the left help page. See all guides.