How to Use VLOOKUP in Google Sheets for Business Inventory (With Examples)

As your small business grows, managing product pricing, stock levels, and supplier data across multiple spreadsheets can quickly become overwhelming. Manually searching for product codes or SKUs wastes time and invites costly manual errors.

The VLOOKUP function in Google Sheets solves this exact problem. It allows you to search for a specific value (like a Product SKU) in one table and automatically pull corresponding information (like Price or Quantity) from another table.

What is VLOOKUP? (Syntax Explained)

VLOOKUP stands for "Vertical Lookup." It searches down the first column of a data range until it finds a match, then moves across to retrieve data from a specified column in that same row.

Formula Syntax:

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

• search_key: The value you want to search for (e.g., SKU Code "SKU-102" or cell A2).
• range: The table range where your data lives (e.g., 'Inventory Master'!A2:D100).
• index: The column number in the range from which to retrieve the value (1 for the 1st column, 2 for the 2nd, etc.).
• is_sorted: Always set this to FALSE (or 0) for exact matches in business spreadsheets.

Step-by-Step Example: Pulling Product Prices

Imagine you have a Master Price List sheet with SKUs in Column A and Unit Prices in Column B. On a separate Sales Invoice sheet, you want Column C to fill in the price automatically whenever you enter a SKU in Column A.

The Formula to Paste:

On your Invoice sheet, click in cell C2 (Unit Price) and enter:

=VLOOKUP(A2, 'Master Price List'!A2:C100, 3, FALSE)

Now, whenever you type SKU-102 in cell A2, Google Sheets will scan the Master Price List and automatically output $8.00.

How to Fix Common VLOOKUP Errors

1. The `#N/A` Error (No Match Found)

If a SKU isn't in your master list yet, VLOOKUP will display an ugly #N/A error. You can clean this up using the IFERROR wrapper:

=IFERROR(VLOOKUP(A2, 'Master Price List'!A2:C100, 3, FALSE), "SKU Not Found")

2. The "Left-Lookup" Limitation

Standard VLOOKUP can only search from left to right. Your search key must always be in the first column of the range. If your SKU column is to the right of your price column, VLOOKUP will fail.

The Modern Upgrade: Switching to XLOOKUP

Google Sheets now supports XLOOKUP, a faster and more flexible replacement for VLOOKUP that can look up data in any direction (left or right) and handles missing values natively.

The Same Query Using XLOOKUP:

=XLOOKUP(A2, 'Master Price List'!A1:A100, 'Master Price List'!C1:C100, "SKU Not Found")

Summary Checklist for Spreadsheet Automation

• Always use FALSE as the 4th parameter in VLOOKUP to prevent incorrect approximate matches.
• Wrap VLOOKUP in IFERROR to keep client-facing invoices and reports looking professional.
• Consider upgrading to XLOOKUP for newer spreadsheets to make your formulas more flexible.