VLOOKUP to the Left in Google Sheets: How It Works

If your lookup values are in C2:C100, the values you want to return are in A2:A100, and the search key is in E2, use =INDEX(A2:A100,MATCH(E2,C2:C100,0)). Google Sheets’ VLOOKUP cannot look left within its table range, but INDEX with MATCH can, and a rearranged virtual table can make VLOOKUP work too.

Why VLOOKUP Cannot Look Left

VLOOKUP searches for a value in the first column of the range you provide, then returns a value from a column to the right. Its column number tells Sheets which column in that range to return; it does not let you specify a return column that comes before the lookup column. For example, if the SKU is in column C and the product name is in column A, a VLOOKUP range beginning at A cannot search column C and then return column A.

This behavior is a built-in limitation of VLOOKUP, not a setting you can switch on. The function is still useful when the lookup column is at the left edge of your data and the result is to its right. For a lookup in either direction, use INDEX and MATCH or XLOOKUP. If a spreadsheet already relies on VLOOKUP, however, you can also build a temporary two-column array with the lookup values first.

Consider a small product table: column A contains product names, column C contains SKUs, and E2 contains the SKU to search for. If E2 contains L-08, the desired result is Desk lamp. The approaches below all work with this layout, though they differ in readability, flexibility, and compatibility.

Use INDEX and MATCH for a Left Lookup

INDEX and MATCH separate the job into two parts. MATCH finds the position of the search key in the lookup range. INDEX uses that position to return the corresponding item from the result range. Because the two ranges are independent, the result range can be to the left, right, or elsewhere on the sheet.

With product names in A2:A4, SKUs in C2:C4, and the SKU to find in E2, use =INDEX(A2:A4,MATCH(E2,C2:C4,0)). If C2:C4 contains M-12, L-08, and N-31 in that order, and E2 contains L-08, MATCH returns 2 and INDEX returns the second name in A2:A4: Desk lamp.

The final argument, 0, asks MATCH for an exact match. That is usually what you want for IDs, names, codes, and other categorical values. Omitting it can lead to approximate-match behavior, which assumes sorted data and can return an unexpected result when the range is not sorted correctly.

To show a friendly message rather than an error when a key is missing, wrap the lookup in IFNA. With the same product-name, SKU, and search-key ranges, use =IFNA(INDEX(A2:A4,MATCH(E2,C2:C4,0)),"Not found"). If E2 contains L-99 and that SKU is absent, the formula returns Not found. IFNA handles the not-found error without masking every possible error; IFERROR is broader and can also hide other problems in a formula.

Set Up the Lookup Step by Step

Identify the column that contains the key you will enter, such as a SKU or employee ID. This is the lookup range.
Identify the column containing the result you need. It can sit on either side of the lookup column, but each range must cover corresponding rows.
Choose a cell for the search key, such as E2, and enter the value to find there.
In a separate result cell, enter an INDEX/MATCH formula using the result range first and the lookup range inside MATCH. Use 0 for an exact match.
Test the formula with a key you know is present, then test a missing key so you can decide whether to display an error or a custom message.

Keep both ranges aligned. If A2:A100 contains the return values, the matching lookup range should cover the same rows, such as C2:C100. Using A2:A100 with C3:C101 shifts the correspondence by one row, so a correct match position can return the wrong record. When you expect to copy the formula down, consider using absolute references for the data ranges—for example, $A$2:$A$100 and $C$2:$C$100—while leaving E2 relative if each row will have its own search key.

Make VLOOKUP Work with a Virtual Table

If you specifically need VLOOKUP, you can present it with a two-column array in which the lookup range comes first and the return range second. The braces create an array for the formula; they do not rearrange or edit the source columns on the sheet.

With SKUs in C2:C4, product names in A2:A4, and the search key in E2, use =VLOOKUP(E2,{C2:C4,A2:A4},2,FALSE). The first array column is the SKU range, and the second is the product-name range, so the column index 2 returns the name. For E2 containing L-08, the result is Desk lamp. FALSE requests an exact match.

This technique is convenient for a quick formula or when maintaining a VLOOKUP-based workbook. Make sure both array ranges have the same number of rows and represent the same records in the same order. If one range starts on row 2 and the other on row 3, the virtual table pairs unrelated values. Also be aware that array-literal separators can vary with spreadsheet locale settings. If Sheets rejects the braces or separators, check the locale under File > Settings and use the array separators accepted by that locale.

The virtual table exists only inside the formula, which can make the construction less obvious to someone reading or editing the sheet later. For a workbook that other people will maintain, INDEX/MATCH is often easier to understand because its two ranges are named directly in the formula.

Choose Between INDEX/MATCH, VLOOKUP, and XLOOKUP

All three methods can return a value from a column to the left in Google Sheets, but they suit different situations:

INDEX/MATCH: A flexible, widely understood option. It works when the return column is on either side of the lookup column and makes the two ranges explicit.
VLOOKUP with an array: Useful when you want to keep a VLOOKUP-style formula, but the temporary table and column index can be harder to read.
XLOOKUP: A more direct option in Google Sheets. It accepts separate lookup and result ranges, so their left-to-right order does not matter.

With SKUs in C2:C4, product names in A2:A4, and the search key in E2, XLOOKUP can be written as =XLOOKUP(E2,C2:C4,A2:A4,"Not found",0). For E2 containing L-08, it returns Desk lamp; for a missing key, it returns Not found. The final 0 specifies exact matching. XLOOKUP is a real Google Sheets function, but if the same file will also be opened in older spreadsheet programs, check that they support it before choosing it. Excel has its own XLOOKUP support in Microsoft 365 and newer versions, while older Excel versions may not recognize it.

For a new Sheets formula, choose the method that your collaborators can readily maintain. INDEX/MATCH is a safe general-purpose choice; XLOOKUP is compact and clear; the array workaround is best when there is a specific reason to retain VLOOKUP.

Common Errors and How to Fix Them

#N/A: MATCH or a lookup function did not find the key. Confirm that it exists in the lookup range and that the formula points to the intended cells. IFNA can replace this result with a message, but it is better to correct a wrong range than merely hide the error.
A surprising result: Check that the formula uses exact matching. In INDEX/MATCH, that means the 0 in MATCH; in VLOOKUP, use FALSE. Also check for duplicate keys: an exact lookup generally returns the first matching row, not every row with that key.
Numbers stored as text: A numeric-looking ID may be stored as text in one place and as a number in another. Formatting a cell to look like a number does not always change its underlying value. Compare the source values and make their types consistent.
Spaces or inconsistent spelling: A trailing space or a different character can make two labels that look similar fail to match. Clean or standardize source data when possible rather than adding increasingly complicated conditions to the lookup.
Wrong row returned: Confirm that the lookup and return ranges start and end on corresponding rows. This is especially important in a VLOOKUP array, where ranges of different heights or shifted starting rows can produce bad pairings.

For large sheets, avoid selecting entire columns unnecessarily if the file becomes slow or difficult to maintain. Bounded ranges such as A2:A5000 are easier to audit, while open-ended ranges can be convenient when records are regularly added. Whichever style you choose, keep the lookup and result ranges consistent.

How This Differs from Excel

The core ideas are similar in Google Sheets and Excel: traditional VLOOKUP searches the first column of its table range, while INDEX/MATCH can return from either side. Google Sheets-specific details matter when sharing a formula, especially the array-literal syntax used to construct a virtual table. Do not assume that every formula entered in one spreadsheet program will behave identically in another without checking compatibility and locale settings.

Google Sheets runs in a web browser, and its function names and formula separators can depend on spreadsheet settings. Excel for Microsoft 365 also offers XLOOKUP, but menus, version support, and some formula behavior differ between products. If a file will be passed between Sheets and Excel, test the actual formulas in both applications and consider INDEX/MATCH for a broadly familiar approach. Avoid replacing a working lookup solely because one application offers a newer shorthand.

Frequently Asked Questions

Can VLOOKUP search to the left in Google Sheets?
Not directly. VLOOKUP returns from columns to the right of the first column in its table range. Use INDEX/MATCH or XLOOKUP, or create an array with the lookup column first if you need to keep VLOOKUP.

What is the simplest formula to return a value to the left?
For many sheets, INDEX with MATCH is straightforward: MATCH finds the row position in the lookup column, and INDEX returns the value at that position from the result column. Make the ranges line up and use exact matching for identifiers.

Does the lookup column have to be sorted?
No, not when you specify exact matching with 0 in MATCH or FALSE in VLOOKUP. Sorting is important for approximate-match lookups, but it is not required for these exact-match examples.

What happens if the lookup value appears more than once?
A standard lookup returns the first matching entry it encounters. If repeated keys represent separate records and you need every match, a single-value lookup is not the right tool; use a filtering approach instead.

Can I use XLOOKUP in Google Sheets?
Yes. XLOOKUP is available in Google Sheets and can use separate lookup and result ranges, including when the result column is to the left. Check compatibility if the spreadsheet will be opened in older software that may not support it.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.