Introduction
In today’s data‑driven environment, quickly locating product prices within a spreadsheet can save hours of manual work. Google Sheets offers powerful lookup tools—VLOOKUP, INDEX, MATCH and ARRAYFORMULA—that, when combined, turn a static price table into a dynamic pricing engine. This article walks you through building a reliable lookup system, starting with a clean table layout, then demonstrating the classic VLOOKUP approach, advancing to the more flexible INDEX/MATCH pair, and finally scaling the solution with ARRAYFORMULA for whole‑column queries. By the end, you’ll be able to retrieve any product’s price instantly, even as new items are added or the table structure changes.
Setting Up the Price Table
Before any formula can work, the data must be organized consistently. Place product identifiers (SKU, name, or code) in the first column and corresponding prices in the second column. Avoid merged cells and keep headers separate from the data range. A typical layout looks like this:
- A1: Product ID
- B1: Price
- A2‑A100: unique identifiers
- B2‑B100: numeric prices
Ensuring that the identifier column is sorted (or not, depending on the lookup method) and that prices are stored as numbers (not text) will prevent common errors such as #N/A or mismatched types.
Basic VLOOKUP for Simple Lookups
The VLOOKUP function is the most straightforward way to pull a price when the lookup column is the leftmost column of the table. The syntax is:
- VLOOKUP(search_key, range, index, [is_sorted])
For example, to find the price of the product code in cell D2:
- =VLOOKUP(D2, A2:B100, 2, FALSE)
The FALSE argument forces an exact match, eliminating errors caused by unsorted data. If the product is not found, VLOOKUP returns #N/A, which you can trap with IFERROR to display a friendly message.
Combining MATCH with INDEX for Flexible Retrieval
While VLOOKUP works well for simple tables, it fails when the lookup column is not the first column or when you need a two‑way lookup. Pairing MATCH with INDEX solves these limitations. MATCH finds the row number of a given key, and INDEX returns the value from any column based on that row.
- MATCH(search_key, lookup_range, 0) – returns the relative row.
- INDEX(return_range, row_number) – fetches the price.
Combined formula:
- =INDEX(B2:B100, MATCH(D2, A2:A100, 0))
This approach lets you place the identifier column anywhere, even to the right of the price column, and still retrieve the correct price. Additionally, you can swap the ranges to perform a reverse lookup (price → product) by matching against the price column first.
Scaling with ARRAYFORMULA for Whole‑Column Lookups
When you need to pull prices for an entire list of products at once, wrapping the lookup inside ARRAYFORMULA eliminates the need to copy the formula down manually. Using the INDEX/MATCH pair inside ARRAYFORMULA looks like this:
- =ARRAYFORMULA(IF(LEN(D2:D), INDEX(B2:B100, MATCH(D2:D, A2:A100, 0)), “”))
The IF(LEN(D2:D), …, “”) part ensures blank rows stay blank, preventing unnecessary errors. For a pure VLOOKUP version, you can use:
- =ARRAYFORMULA(IFERROR(VLOOKUP(D2:D, A2:B100, 2, FALSE), “Not found”))
Both formulas automatically expand as you add new product codes in column D, making the sheet dynamic and maintenance‑free. Remember to keep the source table (A2:B100) open‑ended (e.g., A2:B) if you expect the price list to grow.
Conclusion
By structuring your price table correctly and mastering the interplay of VLOOKUP, INDEX, MATCH, and ARRAYFORMULA, you transform a static list of numbers into a responsive pricing lookup system. VLOOKUP offers quick, one‑dimensional searches, while INDEX/MATCH adds flexibility for multi‑column layouts and reverse lookups. Embedding these functions in ARRAYFORMULA scales the solution to entire columns, eliminating repetitive copying and reducing errors. Apply these techniques to any product catalog, and you’ll enjoy instant, accurate price retrieval that adapts as your inventory evolves.
