VLOOKUP and XLOOKUP in GridCraft: Tested Parameters and Errors

Start with a lookup table you can check by eye

Put Code and Price in A1:B1. Enter four rows: 10 and 100; 20 and 200; 20 and 250; 30 and 300. The repeated code 20 is deliberate. It lets us distinguish the first matching row from the last, while the missing code 25 tests approximate matching. Keep the lookup column numeric throughout this first check.

Download the CSV example and open it in the tested release, or enter the values manually. For a repeatable command-line check, run the expression with gridcraft-cli eval, passing --in lookup.csv and --json. In a worksheet, enter it in an unused cell outside A1:B5. Do not accidentally replace a lookup value with the formula you are testing.

=VLOOKUP(20,A2:B5,2,FALSE)
=XLOOKUP(20,A2:A5,B2:B5)

• Download the four-row lookup example

VLOOKUP: exact, approximate, and invalid column numbers

The supported signature has three required arguments and an optional fourth: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). With FALSE, the code 20 returned 200, the first matching row. Code 25 returned #N/A because no exact record exists. This is a useful failure: it exposes a missing key instead of silently choosing a nearby price.

With TRUE and the ascending example, code 25 returned 250, the price from the last qualifying row with code 20. Approximate matching therefore did not mean “find the closest number in either direction.” Use a deliberately sorted table for this check and do not assume an unsorted price list is safe. Explicitly choose FALSE for identifiers, invoice numbers, and product codes when exact identity matters.

XLOOKUP: fallback values and search direction

XLOOKUP accepts lookup_value, lookup_array, return_array, then optional if_not_found, match_mode, and search_mode. In our example, the three-argument form returned 200. Searching for 99 with a fallback of "missing" returned that text. This is clearer than wrapping every lookup in broad error suppression, because unrelated structural errors should still be investigated.

The optional match mode changed the result for code 25: -1 returned 200 and 1 returned 300. With an exact code 20 and search mode -1, the result became 250, the last duplicate. Search mode 2 returned 200 on the ascending sample. These checks establish those particular cases; descending binary searches, wildcard patterns, and spilled multi-column results were not covered by this test set.

An unsupported match mode of 7 and an unsupported search mode of 7 both returned #VALUE!. Using A2:A5 as the lookup range but B2:B4 as the return range also returned #VALUE!. A fallback string did not turn an invalid mode into a successful lookup. Keep both arrays aligned and the same size before investigating missing keys.

=XLOOKUP(99,A2:A5,B2:B5,"missing")
=XLOOKUP(25,A2:A5,B2:B5,"missing",-1)
=XLOOKUP(20,A2:A5,B2:B5,"missing",0,-1)

Move from the sample to your workbook

First decide whether duplicate keys are valid in your data. If they are, document whether the first or last record should win. Next check whether imported codes are numbers or text: a successful numeric example does not prove that "0012" and 12 are interchangeable identifiers. Check missing keys separately from malformed ranges, and verify a few returned records against their source rows.

Save a copy as XLSX and reopen it in the application where the file will be used. Recheck the missing key and duplicate key, not just one successful result. If an expression is rejected before calculation, inspect the required arguments and separators; if it calculates to an error value, investigate that specific error. The v0.19.1 CLI exits unsuccessfully for the too-few-arguments examples, whereas calculated spreadsheet errors are returned as values in JSON.

• Preserve identifiers when importing CSV
• Check a lookup that appears stuck on an old value