Thousands of VLOOKUP formulas recalculate slowly. A dictionary in Apps Script fills price columns once per refresh.
vlookupReplacementScript reads PriceList into a plain object keyed by uppercase SKU.
LineItems columns C–D become unit price and qty*price; missing SKUs leave blanks instead of #N/A.
Run after price list edits or on a nightly trigger before reporting.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Column | Purpose |
|---|---|---|
| PriceList | A SKU | Lookup key |
| PriceList | B UnitPrice | Value |
| LineItems | A SKU | Input |
| LineItems | B Qty | Input |
| LineItems | C UnitPrice | Filled |
| LineItems | D LineTotal | Filled |
What this script does
Batch dictionary lookup replacing per-row VLOOKUP formulas.
Prerequisites
PriceList and LineItems sheets with the columns above.
- SKU case normalized
- Numeric prices
- LineItems width >= 4
Walkthrough
Change a price, run the function, confirm LineItems totals update.
Edge cases
Duplicate SKUs in PriceList — last row wins in the dict.
- hasOwnProperty avoids missing→undefined bugs
- Blank unit when SKU absent
How to test
Include an unknown SKU; expect blank C/D.
Hardening for production
Cache PriceList in CacheService for multi-sheet workbooks.
Variations
Return nearest tier price; or pull descriptions in column E.
Full code: vlookupReplacementScript()
Maintain PriceList, then run vlookupReplacementScript() to refresh LineItems.
/**
* Dictionary VLOOKUP replacement: map SKUs to prices in batch.
*/
const ITEMS = "LineItems";
const PRICE = "PriceList";
function vlookupReplacementScript() {
const ss = SpreadsheetApp.getActive();
const prices = ss.getSheetByName(PRICE).getDataRange().getValues();
const dict = {};
for (let i = 1; i < prices.length; i++) {
dict[String(prices[i][0]).toUpperCase()] = Number(prices[i][1]);
}
const sheet = ss.getSheetByName(ITEMS);
const values = sheet.getDataRange().getValues();
for (let i = 1; i < values.length; i++) {
const sku = String(values[i][0]).toUpperCase();
const qty = Number(values[i][1]) || 0;
const unit = dict.hasOwnProperty(sku) ? dict[sku] : "";
values[i][2] = unit;
values[i][3] = unit === "" ? "" : unit * qty;
}
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}- Line 12: Builds the SKU→price map.
- Line 12: Case-insensitive SKU match.
- Line 20: Detects missing SKUs safely.
- Line 22: Computes line total.
- Line 24: Writes all computed columns at once.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: VLOOKUP replacement
- 1PriceList complete
- 2LineItems SKU/Qty populated
- 3No critical formulas in C/D you need to keep
- 4Duplicate SKUs in price list resolved
- 5Run after price changes
- 6Spot-check a few totals manually
Frequently asked questions
One read of PriceList plus one write beats thousands of per-cell lookups on each edit.
Implement binary search on a sorted key column — not in this exact sample.
Store arrays in dict[sku] = [price, desc] and unpack.
Yes for small sheets; use scripts when editors lag.
Number() depends on value types from getValues — ensure prices are numbers not text.
Write 'NOT_FOUND' instead of blank when !hasOwnProperty.
Build dict from any key column index — orientation does not matter in script.