VLOOKUP, XLOOKUP and INDEX/MATCH: what happens to lookups when Excel becomes code
Lookups are the backbone of most business spreadsheets. Here's how Codcel carries them into generated code, from small in-sheet tables to billions of rows.
Open almost any pricing, planning or risk workbook and you'll find lookups everywhere: rate tables keyed by age band, postcode factors, product parameters, FX rates. When people try to move a spreadsheet into code by hand, lookups are where most of the subtle bugs come from: an approximate match that should have been exact, a sorted-range assumption, or an off-by-one column index.
Codcel keeps the lookups in your formulas and generates code that runs them.
Supported lookup functions
These work across all of Codcel's table types:
| Function | What it does |
|---|---|
VLOOKUP | Finds a value in the first column and returns a value from the same row |
HLOOKUP | Same, but searching the first row |
XLOOKUP | Modern lookup with exact match by default and an optional "if not found" value |
LOOKUP | Simple lookup in a vector or array |
INDEX | Returns the value at a row and column position |
MATCH | Returns the position of a value in a range |
XMATCH | Extended MATCH with more search modes |
FILTER | Returns the rows that meet a condition |
Dynamic-array functions such as SORT, SORTBY, UNIQUE, CHOOSECOLS, VSTACK and HSTACK are supported too. The lookup and reference function list has the full set.
Pick the right kind of table
Where your lookup data lives decides how the generated code stores and queries it:
| Table type | Size | Storage | Formulas inside the table |
|---|---|---|---|
| Small table | Thousands of rows | In memory | Yes |
| Large table | Up to 1,048,576 rows | Its own sheet (named with a T_ prefix) | No, frozen at import |
| Massive table | Billions of rows | CSV, stored as Parquet and queried with SQL | No, frozen at import |
| CRUD table | Unlimited | PostgreSQL | No, but rows can be created, updated and deleted at runtime |
Small tables suit rate tables and reference data with computed columns. Large and massive tables suit reference data that is too big to calculate in the sheet. CRUD tables suit application data that changes while the system is running.
A typical rating lookup
=XLOOKUP(DriverAge, AgeBands[MinAge], AgeBands[Factor], 1, -1)This finds the factor for the largest minimum age that is less than or equal to the driver's age, falling back to 1. Codcel transpiles the formula itself rather than its result, so the generated code performs the same lookup with the same match mode. The generated tests then check it returns what Excel returned.
Make your lookups robust before you generate
- Prefer exact matches. Use
XLOOKUP,XMATCH, orVLOOKUP(..., FALSE)unless you really mean a banded lookup. - For banded lookups, sort the band column, and say so in a note. An approximate
VLOOKUPover an unsorted column is wrong in Excel too. - Use structured references or named ranges instead of hard-coded
A1:D500ranges, so the table can grow. - Give "not found" an explicit value. XLOOKUP's fourth argument documents what the business expects.
Then test it
Codcel generates tests that run each output with the workbook's own inputs and compare the result with Excel's value. For lookup-heavy models, add cases at the band boundaries (for example ages 24, 25 and 26), which is where hand-written code breaks most often.
See how this works in your language: Excel to Python, Excel to Java, Excel to C#, or browse all languages.
Keep reading
How to convert an Excel spreadsheet to Python without rewriting your formulas
A step-by-step guide to turning an Excel model into a tested Python package: tag inputs and outputs, generate the code, and prove it matches the workbook.
Moving an insurance pricing model out of Excel: a production checklist
Rating engines often start life in Excel. Here is a practical checklist for turning one into a governed, tested pricing service without losing what the actuaries built.
One billion rows from an Excel model: the 1BRC with generated Rust
We pointed Codcel at the One Billion Row Challenge: a 12 GB CSV, an ordinary Excel workbook, and a generated Rust REST server. Here's how it works and what it measured.