LAMBDA idea: XV / XH

[jimmytacks]

Origin

Synced from Notion: LAMBDA: XV / XH

⚠️ Exact signature / spec must be verified with Tim before build. Reconstructed from the competitor "Collecting Scarves" workbook, not finalised — arg order and defaults are tentative.

Problem

XLOOKUP needs two parallel arrays and can't cleanly do: case-sensitive matching, an array of lookups combined with a multi-column return (nested-array #CALC!), or non-contiguous / reordered return columns. The competitor's XV (vertical) / XH (horizontal) wrap XLOOKUP to address these.

Proposed

=XV(lookup_value, table, [return_col_no], [lookup_col_no], [if_not_found], [match_mode], [search_mode], [case_sensitive])
=XH( ... )   ' horizontal twin — rows instead of columns

• table — multi-column table (one argument, not two parallel ranges)
• return_col_no — index (or index list) of return column(s); negative from right; default -1 (last)
• lookup_col_no — index of lookup column; smart default (last if returning col 1, else first)
• case_sensitive — case-sensitive match via unicode encoding

Functional wins over native XLOOKUP (beyond typing speed)

1. Case-sensitive matching — XLOOKUP has no option for this; R ≠ r. The standout justification.
2. Array-of-lookups × multi-column return in one formula, without nested-array #CALC!.
3. Non-contiguous / reordered return columns by index list (native return array must be contiguous and in source order).

Notes

• For a single scalar lookup, XLOOKUP is functionally equal — the value is in the three points above.
• Case-sensitivity depends on a _toUNI-style helper; multi-column array return relies on a BMap-style thunked MAP to avoid #CALC!.
• Confirm final argument order, defaults, and whether all three functional wins are wanted with Tim before building.