A VLOOKUP can look simple until it returns the wrong price, throws a #N/A error, or silently pulls a nearby match you did not intend. Most of those problems come down to a few rules: where the lookup column sits, how the range is selected, and whether the match type is correct.

This guide walks through the Google Sheets VLOOKUP formula step by step, including lookups between tabs, imports from another spreadsheet, and the fixes that solve the errors people see most often.

Understand the Google Sheets VLOOKUP syntax before you build it

VLOOKUP searches vertically through the first column of a selected table. When it finds your value, it returns data from another column on the same row.

The syntax is:

=VLOOKUP(search_key, range, index, [is_sorted])

• search_key is the value you want to find, such as an SKU, employee ID, order number, or email address.
• range is the table where Google Sheets should search. The lookup values must be in the first column of this range.
• index is the number of the column to return within that selected range.
• is_sorted tells Sheets whether to find an exact match or an approximate match. Use FALSE for an exact match.

The major limitation is easy to miss: VLOOKUP can only search the leftmost column of the range you select. If your product code is in column C and the price is in column A, a standard VLOOKUP cannot search column C and return column A without rearranging the range or using a different function.

Build your first exact-match VLOOKUP formula

Imagine your sales sheet contains product codes in column A. A reference table in columns F through H contains the product code, product name, and price. You want the price to appear beside each product code.

In the result cell, enter:

=VLOOKUP(A2,$F$2:$H$20,3,FALSE)

Here is what the formula does:

• A2 is the product code to find.
• $F$2:$H$20 is the reference table. Dollar signs lock the range so it does not move when you copy the formula down.
• 3 returns the third column within the selected range, which is column H in this example.
• FALSE requires an exact product-code match.

After confirming the first result is correct, drag the fill handle down or copy the formula into the remaining rows. The lookup cell will adjust from A2 to A3, A4, and so on, while the reference table remains fixed.

Choose FALSE for most IDs, names, and codes

For order numbers, SKUs, email addresses, employee IDs, and similar identifiers, FALSE is the right default. These values either match exactly or they do not; there is no useful "close enough" result.

Using TRUE, or leaving out the fourth argument, enables approximate matching. That only works safely when the first column is sorted in ascending order and you intentionally want a value from a band or threshold table, such as a commission rate based on sales volume. For ordinary record lookups, approximate matching is a common source of incorrect results.

Use VLOOKUP to pull data from another Google Sheet tab

A Google Sheets VLOOKUP from another sheet tab uses the same function. The only difference is that the range includes the tab name.

For example, if the lookup value is in A2 on your current tab and the catalog data lives on a tab named Product Catalog, use:

=VLOOKUP(A2,'Product Catalog'!$A$2:$D$100,4,FALSE)

This searches the first column of the Product Catalog range and returns the value in its fourth column. The single quotation marks are required when a tab name includes spaces or special characters. They are optional for a simple tab name such as Catalog, but using them consistently does no harm.

Keep the dollar signs around the lookup range if you plan to copy the formula down. Without them, the range shifts one row at a time, and valid matches can disappear after the first row.

Look up data from a different spreadsheet with IMPORTRANGE

When the source data lives in a completely separate Google Sheets file, VLOOKUP needs IMPORTRANGE to access it. IMPORTRANGE connects the destination spreadsheet to a selected tab and range in the source spreadsheet.

A combined formula looks like this:

=VLOOKUP(A2,IMPORTRANGE("spreadsheet_URL","Catalog!A2:D100"),4,FALSE)

Replace spreadsheet_URL with the URL of the source spreadsheet. The imported range must still have the lookup key in its leftmost column.

The first time you connect the files, Google Sheets will show a permission prompt. Click Allow access. If the nested formula does not work, test the import separately in an empty cell first:

=IMPORTRANGE("spreadsheet_URL","Catalog!A2:D100")

Once the imported table appears and permission is granted, add VLOOKUP around it. This separates a connection problem from a lookup problem, which makes troubleshooting much faster.

Make cross-sheet formulas easier to maintain

Do not repeat a long IMPORTRANGE URL in dozens of formulas if the imported data is used regularly. A better approach is to place one import formula on a helper tab, then point your VLOOKUP formulas at that local helper range.

For more advanced ways to combine or process ranges in Google Sheets, see this guide to array formulas in Google Sheets.

Named ranges can also make recurring formulas easier to read. Whatever method you choose, remember that source-file permissions, renamed tabs, deleted columns, and changed ranges can break a lookup that previously worked.

Return values safely when some matches are missing

An unmatched lookup returns #N/A. That is not always a formula failure. It may simply mean the item has not been added to the catalog, the employee is no longer in the directory, or the source data is incomplete.

If you want a cleaner result for expected missing values, wrap the formula in IFNA:

=IFNA(VLOOKUP(A2,$F$2:$H$20,3,FALSE),"Not found")

This displays "Not found" only when VLOOKUP cannot find a match. It is more precise than IFERROR, which can hide unrelated issues such as an invalid column index or a broken imported range.

Use IFERROR only when you have deliberately checked for every error it could suppress. In spreadsheet reporting, a visible error is often more useful than a blank cell that masks a real problem.

Fix the most common Google Sheets VLOOKUP errors

When VLOOKUP fails, check the same five items in order: the lookup value, the first column of the selected range, the index number, the match mode, and the data type. This short sequence catches most issues without rebuilding the formula from scratch.

Fix "VLOOKUP evaluates to an out of bounds range"

This error means your index number is larger than the number of columns in the range. Count columns inside the selected range, not the spreadsheet's column letters.

For example, the range F2:H20 contains three columns: F is 1, G is 2, and H is 3. This formula will fail because it asks for a fourth column:

=VLOOKUP(A2,$F$2:$H$20,4,FALSE)

Fix it by changing the index to 3 or expanding the range to include the needed return column. A common mistake is using a worksheet column number, such as 8 for column H. VLOOKUP does not work that way; it counts only within the selected range.

Fix #N/A when the value appears to exist

If the value looks identical but returns #N/A, inspect the actual cell content. Leading or trailing spaces are frequent culprits, especially in data copied from exports, forms, or other systems.

Use TRIM to remove extra spaces around text. For example:

=VLOOKUP(TRIM(A2),$F$2:$H$20,3,FALSE)

For imported text with invisible nonprinting characters, CLEAN may help: =CLEAN(A2). You may need to clean the source lookup column as well, not just the value being searched.

Also check whether one value is stored as text and the other as a number. A code displayed as 00125 is usually best stored consistently as text, because converting it to a number removes its leading zeroes. Finally, confirm that the selected range begins with the actual lookup column and that your formula uses FALSE when an exact match is required.

Fix incorrect results from approximate matching

If VLOOKUP returns a valid-looking but incorrect value, check the fourth argument immediately. With TRUE or an omitted match argument, Google Sheets can return the nearest lower match rather than the exact value.

Switch to FALSE for identifiers and discrete labels. Keep approximate matching only for deliberately sorted threshold tables, such as tax bands, grading scales, or volume discounts.

Use VLOOKUP across multiple sheets without losing control of your ranges

VLOOKUP searches one range at a time. If product records are split across several tabs, the most maintainable option is usually a consolidated helper table that brings those records into one consistent structure.

For a small, fixed set of tabs, you can combine matching tables into a single virtual range and then run one lookup against it. The important requirement remains unchanged: each source table must place the lookup key in its first column, and every combined section should use the same column layout.

Avoid building a long chain of nested VLOOKUP formulas across many tabs. It becomes difficult to audit, easy to break when a tab is renamed, and slow in large workbooks. If multiple sheets are a permanent part of the process, centralize the data first and keep the reporting formulas simple.

Know when VLOOKUP is not the best lookup function

VLOOKUP is a good fit for straightforward left-to-right tables where the lookup key stays in the first column. It becomes restrictive when you need to return a value from the left side of the key or when table columns are likely to be inserted or moved.

Use XLOOKUP when you need more flexibility

XLOOKUP uses separate lookup and return ranges, so it can retrieve values in either direction. It also has a dedicated argument for missing matches.

An equivalent price lookup could be written as:

=XLOOKUP(A2,F2:F20,H2:H20,"Not found")

Here, Google Sheets searches product codes in F2:F20 and returns prices from H2:H20. Because the return range is separate, you do not need to count a column index. Confirm XLOOKUP is available in the Google Sheets environment you are using before standardizing on it.

Use INDEX and MATCH for durable position-based lookups

INDEX and MATCH are useful when a table changes frequently. MATCH finds the position of the lookup value, while INDEX returns the value from the corresponding position in another range.

For the same example:

=INDEX(H2:H20,MATCH(A2,F2:F20,0))

The 0 in MATCH requests an exact match. Unlike VLOOKUP, this formula does not rely on the return column being a fixed number of columns away from the lookup column.

Make VLOOKUP formulas reliable before sharing the sheet

Before you send a spreadsheet to colleagues or use it in a report, run a quick quality check:

• Use FALSE intentionally for normal exact-match lookups.
• Lock reference tables with absolute references, such as $F$2:$H$20, before copying formulas down.
• Make sure lookup keys are unique when you expect one result per item. VLOOKUP returns the first match it finds.
• Standardize the format of lookup values, especially numbers, dates, and codes with leading zeroes.
• Test one known match and one known non-match before filling formulas across a large report.
• For IMPORTRANGE formulas, document the source tab and confirm that recipients have the required file access.

These checks take a few minutes, but they prevent the far more expensive problem of sharing a report with silently incorrect values.

Apply VLOOKUP with confidence in Google Sheets

A dependable VLOOKUP starts with the right table structure: put the lookup key in the first column of the selected range, use the correct return-column index, and choose FALSE for typical IDs, codes, and names.

When a formula fails, do not guess. Check the range boundaries, index number, match setting, and data consistency in that order. And when VLOOKUP's left-to-right limitation gets in the way, switch to XLOOKUP or INDEX/MATCH instead of forcing a fragile workaround.

If you need help generating, explaining, or correcting a Google Sheets formula from a plain-language request, FormulaBerry can help you turn the task into a usable formula.