formualizer_eval/builtins/lookup/
core.rs

1//! Classic lookup & reference essentials: MATCH, VLOOKUP, HLOOKUP (Sprint 4 subset)
2//!
3//! Implementation notes:
4//! - MATCH supports match_type: 0 exact, 1 approximate (largest <= lookup), -1 approximate (smallest >= lookup)
5//! - Approximate modes assume data sorted ascending (1) or descending (-1); unsorted leads to #N/A like Excel (we don't yet detect unsorted reliably, TODO)
6//! - Binary search used for approximate modes for efficiency; linear scan for exact or when data small (<8 elements) to avoid overhead.
7//! - VLOOKUP/HLOOKUP wrap MATCH logic; VLOOKUP: vertical first column; HLOOKUP: horizontal first row.
8//! - Error propagation: if lookup_value is error -> propagate. If table/range contains errors in non-deciding positions, they don't matter unless selected.
9//! - Type coercion: current simple: numbers vs numeric text coerced; text comparison case-insensitive? Excel is case-insensitive for MATCH (without wildcards). We implement case-insensitive for now.
10//!   TODO(excel-nuance): refine boolean/text/number coercion differences.
11
12use super::lookup_utils::{cmp_for_lookup, find_exact_index, is_sorted_ascending};
13use crate::args::{ArgSchema, CoercionPolicy, ShapeKind};
14use crate::function::Function;
15use crate::traits::{ArgumentHandle, FunctionContext};
16use formualizer_common::ArgKind;
17use formualizer_common::{ExcelError, ExcelErrorKind, LiteralValue};
18use formualizer_macros::func_caps;
19use formualizer_parse::parser::ReferenceType;
20
21fn binary_search_match(slice: &[LiteralValue], needle: &LiteralValue, mode: i32) -> Option<usize> {
22    if mode == 0 || slice.is_empty() {
23        return None;
24    }
25    // Only ascending binary search currently (mode 1); descending path kept linear for now.
26    if mode == 1 {
27        // largest <= needle
28        let mut lo = 0usize;
29        let mut hi = slice.len();
30        while lo < hi {
31            let mid = (lo + hi) / 2;
32            match cmp_for_lookup(&slice[mid], needle) {
33                Some(c) => {
34                    if c > 0 {
35                        hi = mid;
36                    } else {
37                        lo = mid + 1;
38                    }
39                }
40                None => {
41                    hi = mid;
42                }
43            }
44        }
45        if lo == 0 { None } else { Some(lo - 1) }
46    } else {
47        // -1 mode handled via linear fallback since semantics differ (smallest >=)
48        let mut best: Option<usize> = None;
49        for (i, v) in slice.iter().enumerate() {
50            if let Some(c) = cmp_for_lookup(v, needle) {
51                if c == 0 {
52                    return Some(i);
53                }
54                if c >= 0 && best.is_none_or(|b| i < b) {
55                    best = Some(i);
56                }
57            }
58        }
59        best
60    }
61}
62
63#[derive(Debug)]
64pub struct MatchFn;
65impl Function for MatchFn {
66    fn name(&self) -> &'static str {
67        "MATCH"
68    }
69    fn min_args(&self) -> usize {
70        2
71    }
72    func_caps!(PURE, LOOKUP);
73    fn arg_schema(&self) -> &'static [ArgSchema] {
74        use once_cell::sync::Lazy;
75        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
76            vec![
77                // lookup_value (any scalar)
78                ArgSchema {
79                    kinds: smallvec::smallvec![ArgKind::Any],
80                    required: true,
81                    by_ref: false,
82                    shape: ShapeKind::Scalar,
83                    coercion: CoercionPolicy::None,
84                    max: None,
85                    repeating: None,
86                    default: None,
87                },
88                // lookup_array (by-ref range)
89                ArgSchema {
90                    kinds: smallvec::smallvec![ArgKind::Range],
91                    required: true,
92                    by_ref: true,
93                    shape: ShapeKind::Range,
94                    coercion: CoercionPolicy::None,
95                    max: None,
96                    repeating: None,
97                    default: None,
98                },
99                // match_type (optional numeric, default 1)
100                ArgSchema {
101                    kinds: smallvec::smallvec![ArgKind::Number],
102                    required: false,
103                    by_ref: false,
104                    shape: ShapeKind::Scalar,
105                    coercion: CoercionPolicy::NumberLenientText,
106                    max: None,
107                    repeating: None,
108                    default: Some(LiteralValue::Number(1.0)),
109                },
110            ]
111        });
112        &SCHEMA
113    }
114    fn eval_scalar<'a, 'b>(
115        &self,
116        args: &'a [ArgumentHandle<'a, 'b>],
117        ctx: &dyn FunctionContext,
118    ) -> Result<LiteralValue, ExcelError> {
119        if args.len() < 2 {
120            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
121        }
122        let lookup_value = match args[0].value() {
123            Ok(v) => v,
124            Err(e) => return Ok(LiteralValue::Error(e)),
125        }; // propagate as value error
126        let mut match_type = 1.0; // default
127        if args.len() >= 3 {
128            let mt_val = match args[2].value() {
129                Ok(v) => v,
130                Err(e) => return Ok(LiteralValue::Error(e)),
131            };
132            if let LiteralValue::Error(e) = mt_val.as_ref() {
133                return Ok(LiteralValue::Error(e.clone()));
134            }
135            match mt_val.as_ref() {
136                LiteralValue::Number(n) => match_type = *n,
137                LiteralValue::Int(i) => match_type = *i as f64,
138                LiteralValue::Text(s) => {
139                    if let Ok(n) = s.parse::<f64>() {
140                        match_type = n;
141                    }
142                }
143                _ => {}
144            }
145        }
146        let mt = if match_type > 0.0 {
147            1
148        } else if match_type < 0.0 {
149            -1
150        } else {
151            0
152        };
153        let arr_ref = args[1].as_reference_or_eval().ok();
154        let mut values: Vec<LiteralValue> = Vec::new();
155        if let Some(r) = arr_ref {
156            let current_sheet = ctx.current_sheet();
157            match ctx.resolve_range_view(&r, current_sheet) {
158                Ok(rv) => {
159                    if let Err(e) = rv.for_each_cell(&mut |v| {
160                        values.push(v.clone());
161                        Ok(())
162                    }) {
163                        return Ok(LiteralValue::Error(e));
164                    }
165                }
166                Err(e) => return Ok(LiteralValue::Error(e)),
167            }
168        } else {
169            match args[1].value() {
170                Ok(v) => values.push(v.as_ref().clone()),
171                Err(e) => return Ok(LiteralValue::Error(e)),
172            }
173        }
174        if values.is_empty() {
175            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
176        }
177        if mt == 0 {
178            let wildcard_mode = matches!(lookup_value.as_ref(), LiteralValue::Text(s) if s.contains('*') || s.contains('?') || s.contains('~'));
179            if let Some(idx) = find_exact_index(&values, lookup_value.as_ref(), wildcard_mode) {
180                return Ok(LiteralValue::Int((idx + 1) as i64));
181            }
182            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
183        }
184        // Lightweight unsorted detection for approximate modes
185        let is_sorted = if mt == 1 {
186            is_sorted_ascending(&values)
187        } else if mt == -1 {
188            values
189                .windows(2)
190                .all(|w| cmp_for_lookup(&w[0], &w[1]).is_some_and(|c| c >= 0))
191        } else {
192            true
193        };
194        if !is_sorted {
195            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
196        }
197        let idx = if values.len() < 8 {
198            // linear small
199            let mut best: Option<(usize, &LiteralValue)> = None;
200            for (i, v) in values.iter().enumerate() {
201                if let Some(c) = cmp_for_lookup(v, lookup_value.as_ref()) {
202                    // compare candidate to needle
203                    if mt == 1 {
204                        // v <= needle
205                        if (c == 0 || c == -1) && (best.is_none() || i > best.unwrap().0) {
206                            best = Some((i, v));
207                        }
208                    } else {
209                        // -1, v >= needle
210                        if (c == 0 || c == 1) && (best.is_none() || i > best.unwrap().0) {
211                            best = Some((i, v));
212                        }
213                    }
214                }
215            }
216            best.map(|(i, _)| i)
217        } else {
218            binary_search_match(&values, lookup_value.as_ref(), mt)
219        };
220        match idx {
221            Some(i) => Ok(LiteralValue::Int((i + 1) as i64)),
222            None => Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na))),
223        }
224    }
225}
226
227#[derive(Debug)]
228pub struct VLookupFn;
229impl Function for VLookupFn {
230    fn name(&self) -> &'static str {
231        "VLOOKUP"
232    }
233    fn min_args(&self) -> usize {
234        3
235    }
236    func_caps!(PURE, LOOKUP);
237    fn arg_schema(&self) -> &'static [ArgSchema] {
238        use once_cell::sync::Lazy;
239        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
240            vec![
241                // lookup_value
242                ArgSchema {
243                    kinds: smallvec::smallvec![ArgKind::Any],
244                    required: true,
245                    by_ref: false,
246                    shape: ShapeKind::Scalar,
247                    coercion: CoercionPolicy::None,
248                    max: None,
249                    repeating: None,
250                    default: None,
251                },
252                // table_array (by-ref)
253                ArgSchema {
254                    kinds: smallvec::smallvec![ArgKind::Range],
255                    required: true,
256                    by_ref: true,
257                    shape: ShapeKind::Range,
258                    coercion: CoercionPolicy::None,
259                    max: None,
260                    repeating: None,
261                    default: None,
262                },
263                // col_index_num (strict number)
264                ArgSchema {
265                    kinds: smallvec::smallvec![ArgKind::Number],
266                    required: true,
267                    by_ref: false,
268                    shape: ShapeKind::Scalar,
269                    coercion: CoercionPolicy::NumberStrict,
270                    max: None,
271                    repeating: None,
272                    default: None,
273                },
274                // range_lookup (optional logical, default FALSE for safer exact default)
275                ArgSchema {
276                    kinds: smallvec::smallvec![ArgKind::Logical],
277                    required: false,
278                    by_ref: false,
279                    shape: ShapeKind::Scalar,
280                    coercion: CoercionPolicy::Logical,
281                    max: None,
282                    repeating: None,
283                    default: Some(LiteralValue::Boolean(false)),
284                },
285            ]
286        });
287        &SCHEMA
288    }
289    fn eval_scalar<'a, 'b>(
290        &self,
291        args: &'a [ArgumentHandle<'a, 'b>],
292        ctx: &dyn FunctionContext,
293    ) -> Result<LiteralValue, ExcelError> {
294        if args.len() < 3 {
295            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
296        }
297        let lookup_value = match args[0].value() {
298            Ok(v) => v,
299            Err(e) => return Ok(LiteralValue::Error(e)),
300        };
301        let table_ref = match args[1].as_reference_or_eval() {
302            Ok(r) => r,
303            Err(e) => return Ok(LiteralValue::Error(e)),
304        };
305        let col_index = match args[2].value()?.as_ref() {
306            LiteralValue::Int(i) => *i,
307            LiteralValue::Number(n) => *n as i64,
308            _ => return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value))),
309        };
310        if col_index < 1 {
311            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
312        }
313        let approximate = if args.len() >= 4 {
314            match args[3].value()?.as_ref() {
315                LiteralValue::Boolean(b) => *b,
316                _ => true,
317            }
318        } else {
319            false // engine chooses FALSE default (exact) rather than Excel's historical TRUE to avoid silent approximate matches
320        };
321        let (sheet, sr, sc, er, ec) = match &table_ref {
322            ReferenceType::Range {
323                sheet,
324                start_row: Some(sr),
325                start_col: Some(sc),
326                end_row: Some(er),
327                end_col: Some(ec),
328            } => (sheet.clone(), *sr, *sc, *er, *ec),
329            _ => return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Ref))),
330        };
331        let current_sheet = ctx.current_sheet();
332        let sheet_name = sheet.as_deref().unwrap_or(current_sheet);
333        let width = ec - sc + 1;
334        if col_index as u32 > width {
335            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Ref)));
336        }
337        // Collect first column
338        let mut first_col: Vec<LiteralValue> = Vec::new();
339        {
340            let rv = ctx.resolve_range_view(&table_ref, sheet_name)?;
341            let col_offset = 0usize;
342            rv.for_each_row(&mut |row| {
343                let v = row.get(col_offset).cloned().unwrap_or(LiteralValue::Empty);
344                first_col.push(v);
345                Ok(())
346            })?;
347        }
348        let row_idx_opt = if approximate {
349            if first_col.is_empty() {
350                None
351            } else {
352                binary_search_match(&first_col, lookup_value.as_ref(), 1)
353            }
354        } else {
355            let mut found = None;
356            for (i, v) in first_col.iter().enumerate() {
357                if let Some(c) = cmp_for_lookup(lookup_value.as_ref(), v)
358                    && c == 0
359                {
360                    found = Some(i);
361                    break;
362                }
363            }
364            found
365        };
366        let row_idx = match row_idx_opt {
367            Some(i) => i,
368            None => return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na))),
369        };
370        // Retrieve target by re-iterating rows (acceptable initial implementation)
371        let rv = ctx.resolve_range_view(&table_ref, sheet_name)?;
372        let mut current = 0usize;
373        let target_col_idx = ((sc + (col_index as u32) - 1) - sc) as usize; // zero-based within slice
374        let mut out: Option<LiteralValue> = None;
375        rv.for_each_row(&mut |row| {
376            if current == row_idx {
377                out = Some(
378                    row.get(target_col_idx)
379                        .cloned()
380                        .unwrap_or(LiteralValue::Empty),
381                );
382            }
383            current += 1;
384            Ok(())
385        })?;
386        Ok(out.unwrap_or(LiteralValue::Empty))
387    }
388}
389
390#[derive(Debug)]
391pub struct HLookupFn;
392impl Function for HLookupFn {
393    fn name(&self) -> &'static str {
394        "HLOOKUP"
395    }
396    fn min_args(&self) -> usize {
397        3
398    }
399    func_caps!(PURE, LOOKUP);
400    fn arg_schema(&self) -> &'static [ArgSchema] {
401        use once_cell::sync::Lazy;
402        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
403            vec![
404                // lookup_value
405                ArgSchema {
406                    kinds: smallvec::smallvec![ArgKind::Any],
407                    required: true,
408                    by_ref: false,
409                    shape: ShapeKind::Scalar,
410                    coercion: CoercionPolicy::None,
411                    max: None,
412                    repeating: None,
413                    default: None,
414                },
415                // table_array (by-ref)
416                ArgSchema {
417                    kinds: smallvec::smallvec![ArgKind::Range],
418                    required: true,
419                    by_ref: true,
420                    shape: ShapeKind::Range,
421                    coercion: CoercionPolicy::None,
422                    max: None,
423                    repeating: None,
424                    default: None,
425                },
426                // row_index_num (strict number)
427                ArgSchema {
428                    kinds: smallvec::smallvec![ArgKind::Number],
429                    required: true,
430                    by_ref: false,
431                    shape: ShapeKind::Scalar,
432                    coercion: CoercionPolicy::NumberStrict,
433                    max: None,
434                    repeating: None,
435                    default: None,
436                },
437                // range_lookup (optional logical, default FALSE for safer exact default)
438                ArgSchema {
439                    kinds: smallvec::smallvec![ArgKind::Logical],
440                    required: false,
441                    by_ref: false,
442                    shape: ShapeKind::Scalar,
443                    coercion: CoercionPolicy::Logical,
444                    max: None,
445                    repeating: None,
446                    default: Some(LiteralValue::Boolean(false)),
447                },
448            ]
449        });
450        &SCHEMA
451    }
452    fn eval_scalar<'a, 'b>(
453        &self,
454        args: &'a [ArgumentHandle<'a, 'b>],
455        ctx: &dyn FunctionContext,
456    ) -> Result<LiteralValue, ExcelError> {
457        if args.len() < 3 {
458            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
459        }
460        let lookup_value = match args[0].value() {
461            Ok(v) => v,
462            Err(e) => return Ok(LiteralValue::Error(e)),
463        };
464        let table_ref = match args[1].as_reference_or_eval() {
465            Ok(r) => r,
466            Err(e) => return Ok(LiteralValue::Error(e)),
467        };
468        let row_index = match args[2].value()?.as_ref() {
469            LiteralValue::Int(i) => *i,
470            LiteralValue::Number(n) => *n as i64,
471            _ => return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value))),
472        };
473        if row_index < 1 {
474            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Value)));
475        }
476        let approximate = if args.len() >= 4 {
477            match args[3].value()?.as_ref() {
478                LiteralValue::Boolean(b) => *b,
479                _ => true,
480            }
481        } else {
482            false
483        };
484        let (sheet, sr, sc, er, ec) = match &table_ref {
485            ReferenceType::Range {
486                sheet,
487                start_row: Some(sr),
488                start_col: Some(sc),
489                end_row: Some(er),
490                end_col: Some(ec),
491            } => (sheet.clone(), *sr, *sc, *er, *ec),
492            _ => return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Ref))),
493        };
494        let current_sheet = ctx.current_sheet();
495        let sheet_name = sheet.as_deref().unwrap_or(current_sheet);
496        let height = er - sr + 1;
497        if row_index as u32 > height {
498            return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Ref)));
499        }
500        let mut first_row: Vec<LiteralValue> = Vec::new();
501        {
502            let rv = ctx.resolve_range_view(&table_ref, sheet_name)?;
503            let mut row_counter = 0usize;
504            rv.for_each_row(&mut |row| {
505                if row_counter == 0 {
506                    first_row.extend_from_slice(row);
507                }
508                row_counter += 1;
509                Ok(())
510            })?;
511        }
512        let col_idx_opt = if approximate {
513            binary_search_match(&first_row, lookup_value.as_ref(), 1)
514        } else {
515            let mut f = None;
516            for (i, v) in first_row.iter().enumerate() {
517                if let Some(c) = cmp_for_lookup(lookup_value.as_ref(), v)
518                    && c == 0
519                {
520                    f = Some(i);
521                    break;
522                }
523            }
524            f
525        };
526        let col_idx = match col_idx_opt {
527            Some(i) => i,
528            None => return Ok(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na))),
529        };
530        let target_row_rel = (row_index as usize) - 1; // zero-based
531        let target_col_rel = col_idx; // zero-based within row slice
532        let rv = ctx.resolve_range_view(&table_ref, sheet_name)?;
533        let mut collected: Option<LiteralValue> = None;
534        let mut r_counter = 0usize;
535        rv.for_each_row(&mut |row| {
536            if r_counter == target_row_rel {
537                collected = Some(
538                    row.get(target_col_rel)
539                        .cloned()
540                        .unwrap_or(LiteralValue::Empty),
541                );
542            }
543            r_counter += 1;
544            Ok(())
545        })?;
546        Ok(collected.unwrap_or(LiteralValue::Empty))
547    }
548}
549
550#[cfg(test)]
551mod tests {
552    use super::*;
553    use crate::test_workbook::TestWorkbook;
554    use crate::traits::ArgumentHandle;
555    use formualizer_parse::parser::{ASTNode, ASTNodeType, ReferenceType};
556    use std::sync::Arc;
557    fn lit(v: LiteralValue) -> ASTNode {
558        ASTNode::new(ASTNodeType::Literal(v), None)
559    }
560
561    #[test]
562    fn match_wildcard_and_descending_and_unsorted() {
563        // Wildcard: A1:A4 = "foo", "fob", "bar", "baz"
564        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
565        let wb = wb
566            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("foo".into()))
567            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("fob".into()))
568            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("bar".into()))
569            .with_cell_a1("Sheet1", "A4", LiteralValue::Text("baz".into()));
570        let ctx = wb.interpreter();
571        let range = ASTNode::new(
572            ASTNodeType::Reference {
573                original: "A1:A4".into(),
574                reference: ReferenceType::Range {
575                    sheet: None,
576                    start_row: Some(1),
577                    start_col: Some(1),
578                    end_row: Some(4),
579                    end_col: Some(1),
580                },
581            },
582            None,
583        );
584        let f = ctx.context.get_function("", "MATCH").unwrap();
585        // Wildcard *o* matches "foo" (1) and "fob" (2), should return first match (1)
586        let pat = lit(LiteralValue::Text("*o*".into()));
587        let zero = lit(LiteralValue::Int(0));
588        let args = vec![
589            ArgumentHandle::new(&pat, &ctx),
590            ArgumentHandle::new(&range, &ctx),
591            ArgumentHandle::new(&zero, &ctx),
592        ];
593        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
594        assert_eq!(v, LiteralValue::Int(1));
595        // Wildcard b?z matches "baz" (4)
596        let pat2 = lit(LiteralValue::Text("b?z".into()));
597        let args2 = vec![
598            ArgumentHandle::new(&pat2, &ctx),
599            ArgumentHandle::new(&range, &ctx),
600            ArgumentHandle::new(&zero, &ctx),
601        ];
602        let v2 = f.dispatch(&args2, &ctx.function_context(None)).unwrap();
603        assert_eq!(v2, LiteralValue::Int(4));
604        // No match
605        let pat3 = lit(LiteralValue::Text("z*".into()));
606        let args3 = vec![
607            ArgumentHandle::new(&pat3, &ctx),
608            ArgumentHandle::new(&range, &ctx),
609            ArgumentHandle::new(&zero, &ctx),
610        ];
611        let v3 = f.dispatch(&args3, &ctx.function_context(None)).unwrap();
612        assert!(matches!(v3, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
613
614        // Descending approximate: 50,40,30,20,10; match_type = -1
615        let wb2 = TestWorkbook::new()
616            .with_function(Arc::new(MatchFn))
617            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(50))
618            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(40))
619            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
620            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(20))
621            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(10));
622        let ctx2 = wb2.interpreter();
623        let range2 = ASTNode::new(
624            ASTNodeType::Reference {
625                original: "A1:A5".into(),
626                reference: ReferenceType::Range {
627                    sheet: None,
628                    start_row: Some(1),
629                    start_col: Some(1),
630                    end_row: Some(5),
631                    end_col: Some(1),
632                },
633            },
634            None,
635        );
636        let minus1 = lit(LiteralValue::Int(-1));
637        let thirty = lit(LiteralValue::Int(30));
638        let args_desc = vec![
639            ArgumentHandle::new(&thirty, &ctx2),
640            ArgumentHandle::new(&range2, &ctx2),
641            ArgumentHandle::new(&minus1, &ctx2),
642        ];
643        let v_desc = f
644            .dispatch(&args_desc, &ctx2.function_context(None))
645            .unwrap();
646        assert_eq!(v_desc, LiteralValue::Int(3));
647        // Descending, not found (needle > max)
648        let sixty = lit(LiteralValue::Int(60));
649        let args_desc2 = vec![
650            ArgumentHandle::new(&sixty, &ctx2),
651            ArgumentHandle::new(&range2, &ctx2),
652            ArgumentHandle::new(&minus1, &ctx2),
653        ];
654        let v_desc2 = f
655            .dispatch(&args_desc2, &ctx2.function_context(None))
656            .unwrap();
657        assert!(matches!(v_desc2, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
658
659        // Unsorted detection: 10, 30, 20, 40, 50 (not sorted ascending)
660        let wb3 = TestWorkbook::new()
661            .with_function(Arc::new(MatchFn))
662            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
663            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
664            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20))
665            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(40))
666            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(50));
667        let ctx3 = wb3.interpreter();
668        let range3 = ASTNode::new(
669            ASTNodeType::Reference {
670                original: "A1:A5".into(),
671                reference: ReferenceType::Range {
672                    sheet: None,
673                    start_row: Some(1),
674                    start_col: Some(1),
675                    end_row: Some(5),
676                    end_col: Some(1),
677                },
678            },
679            None,
680        );
681        let args_unsorted = vec![
682            ArgumentHandle::new(&thirty, &ctx3),
683            ArgumentHandle::new(&range3, &ctx3),
684        ];
685        let v_unsorted = f
686            .dispatch(&args_unsorted, &ctx3.function_context(None))
687            .unwrap();
688        assert!(matches!(v_unsorted, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
689        // Unsorted detection descending: 50, 30, 40, 20, 10
690        let wb4 = TestWorkbook::new()
691            .with_function(Arc::new(MatchFn))
692            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(50))
693            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
694            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(40))
695            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(20))
696            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(10));
697        let ctx4 = wb4.interpreter();
698        let range4 = ASTNode::new(
699            ASTNodeType::Reference {
700                original: "A1:A5".into(),
701                reference: ReferenceType::Range {
702                    sheet: None,
703                    start_row: Some(1),
704                    start_col: Some(1),
705                    end_row: Some(5),
706                    end_col: Some(1),
707                },
708            },
709            None,
710        );
711        let args_unsorted_desc = vec![
712            ArgumentHandle::new(&thirty, &ctx4),
713            ArgumentHandle::new(&range4, &ctx4),
714            ArgumentHandle::new(&minus1, &ctx4),
715        ];
716        let v_unsorted_desc = f
717            .dispatch(&args_unsorted_desc, &ctx4.function_context(None))
718            .unwrap();
719        assert!(matches!(v_unsorted_desc, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
720    }
721
722    #[test]
723    fn match_exact_and_approx() {
724        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
725        let wb = wb
726            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
727            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
728            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
729            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(40))
730            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(50));
731        let ctx = wb.interpreter();
732        let range = ASTNode::new(
733            ASTNodeType::Reference {
734                original: "A1:A5".into(),
735                reference: ReferenceType::Range {
736                    sheet: None,
737                    start_row: Some(1),
738                    start_col: Some(1),
739                    end_row: Some(5),
740                    end_col: Some(1),
741                },
742            },
743            None,
744        );
745        let f = ctx.context.get_function("", "MATCH").unwrap();
746        let thirty = lit(LiteralValue::Int(30));
747        let zero = lit(LiteralValue::Int(0));
748        let args = vec![
749            ArgumentHandle::new(&thirty, &ctx),
750            ArgumentHandle::new(&range, &ctx),
751            ArgumentHandle::new(&zero, &ctx),
752        ];
753        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
754        assert_eq!(v, LiteralValue::Int(3));
755        let thirty_seven = lit(LiteralValue::Int(37));
756        let args = vec![
757            ArgumentHandle::new(&thirty_seven, &ctx),
758            ArgumentHandle::new(&range, &ctx),
759        ];
760        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
761        assert_eq!(v, LiteralValue::Int(3));
762    }
763
764    #[test]
765    fn match_lookup_value_error_propagates() {
766        let wb = TestWorkbook::new()
767            .with_function(Arc::new(MatchFn))
768            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1));
769        let ctx = wb.interpreter();
770        let range = ASTNode::new(
771            ASTNodeType::Reference {
772                original: "A1".into(),
773                reference: ReferenceType::Range {
774                    sheet: None,
775                    start_row: Some(1),
776                    start_col: Some(1),
777                    end_row: Some(1),
778                    end_col: Some(1),
779                },
780            },
781            None,
782        );
783        let f = ctx.context.get_function("", "MATCH").unwrap();
784        let err_lookup = lit(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Div)));
785        let zero = lit(LiteralValue::Int(0));
786        let args = vec![
787            ArgumentHandle::new(&err_lookup, &ctx),
788            ArgumentHandle::new(&range, &ctx),
789            ArgumentHandle::new(&zero, &ctx),
790        ];
791        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
792        // Current validation path collapses error in lookup_value into value position without early propagation.
793        // Accept either passthrough or generic #N/A fallback depending on future semantics.
794        assert!(matches!(v, LiteralValue::Error(_)));
795    }
796
797    #[test]
798    fn vlookup_negative_and_approximate() {
799        let wb = TestWorkbook::new()
800            .with_function(Arc::new(VLookupFn))
801            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
802            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
803            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
804            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("Ten".into()))
805            .with_cell_a1("Sheet1", "B2", LiteralValue::Text("Twenty".into()))
806            .with_cell_a1("Sheet1", "B3", LiteralValue::Text("Thirty".into()));
807        let ctx = wb.interpreter();
808        let table = ASTNode::new(
809            ASTNodeType::Reference {
810                original: "A1:B3".into(),
811                reference: ReferenceType::Range {
812                    sheet: None,
813                    start_row: Some(1),
814                    start_col: Some(1),
815                    end_row: Some(3),
816                    end_col: Some(2),
817                },
818            },
819            None,
820        );
821        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
822        // Negative col_index -> #VALUE!
823        let fifteen = lit(LiteralValue::Int(15));
824        let neg_one = lit(LiteralValue::Int(-1));
825        let true_lit = lit(LiteralValue::Boolean(true));
826        let args_neg = vec![
827            ArgumentHandle::new(&fifteen, &ctx),
828            ArgumentHandle::new(&table, &ctx),
829            ArgumentHandle::new(&neg_one, &ctx),
830            ArgumentHandle::new(&true_lit, &ctx),
831        ];
832        let v_neg = f.dispatch(&args_neg, &ctx.function_context(None)).unwrap();
833        assert!(matches!(v_neg, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
834        // Approximate TRUE should find largest <= 15 -> 10 (row 1)
835        let two = lit(LiteralValue::Int(2));
836        let args_approx = vec![
837            ArgumentHandle::new(&fifteen, &ctx),
838            ArgumentHandle::new(&table, &ctx),
839            ArgumentHandle::new(&two, &ctx),
840            ArgumentHandle::new(&true_lit, &ctx),
841        ];
842        let v_approx = f
843            .dispatch(&args_approx, &ctx.function_context(None))
844            .unwrap();
845        assert_eq!(v_approx, LiteralValue::Text("Ten".into()));
846    }
847
848    #[test]
849    fn hlookup_negative_and_approximate() {
850        let wb = TestWorkbook::new()
851            .with_function(Arc::new(HLookupFn))
852            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
853            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
854            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
855            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Ten".into()))
856            .with_cell_a1("Sheet1", "B2", LiteralValue::Text("Twenty".into()))
857            .with_cell_a1("Sheet1", "C2", LiteralValue::Text("Thirty".into()));
858        let ctx = wb.interpreter();
859        let table = ASTNode::new(
860            ASTNodeType::Reference {
861                original: "A1:C2".into(),
862                reference: ReferenceType::Range {
863                    sheet: None,
864                    start_row: Some(1),
865                    start_col: Some(1),
866                    end_row: Some(2),
867                    end_col: Some(3),
868                },
869            },
870            None,
871        );
872        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
873        let fifteen = lit(LiteralValue::Int(15));
874        let neg_one = lit(LiteralValue::Int(-1));
875        let true_lit = lit(LiteralValue::Boolean(true));
876        let args_neg = vec![
877            ArgumentHandle::new(&fifteen, &ctx),
878            ArgumentHandle::new(&table, &ctx),
879            ArgumentHandle::new(&neg_one, &ctx),
880            ArgumentHandle::new(&true_lit, &ctx),
881        ];
882        let v_neg = f.dispatch(&args_neg, &ctx.function_context(None)).unwrap();
883        assert!(matches!(v_neg, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
884        // Approximate TRUE should find largest <= 15 -> 10 (col 1)
885        let two = lit(LiteralValue::Int(2));
886        let args_approx = vec![
887            ArgumentHandle::new(&fifteen, &ctx),
888            ArgumentHandle::new(&table, &ctx),
889            ArgumentHandle::new(&two, &ctx),
890            ArgumentHandle::new(&true_lit, &ctx),
891        ];
892        let v_approx = f
893            .dispatch(&args_approx, &ctx.function_context(None))
894            .unwrap();
895        assert_eq!(v_approx, LiteralValue::Text("Ten".into()));
896    }
897
898    #[test]
899    fn vlookup_basic() {
900        let wb = TestWorkbook::new()
901            .with_function(Arc::new(VLookupFn))
902            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Key1".into()))
903            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Key2".into()))
904            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(100))
905            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200));
906        let ctx = wb.interpreter();
907        let table = ASTNode::new(
908            ASTNodeType::Reference {
909                original: "A1:B2".into(),
910                reference: ReferenceType::Range {
911                    sheet: None,
912                    start_row: Some(1),
913                    start_col: Some(1),
914                    end_row: Some(2),
915                    end_col: Some(2),
916                },
917            },
918            None,
919        );
920        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
921        let key2 = lit(LiteralValue::Text("Key2".into()));
922        let two = lit(LiteralValue::Int(2));
923        let false_lit = lit(LiteralValue::Boolean(false));
924        let args = vec![
925            ArgumentHandle::new(&key2, &ctx),
926            ArgumentHandle::new(&table, &ctx),
927            ArgumentHandle::new(&two, &ctx),
928            ArgumentHandle::new(&false_lit, &ctx),
929        ];
930        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
931        assert_eq!(v, LiteralValue::Int(200));
932    }
933
934    #[test]
935    fn vlookup_default_exact_behavior() {
936        let wb = TestWorkbook::new()
937            .with_function(Arc::new(VLookupFn))
938            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
939            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
940            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("Ten".into()))
941            .with_cell_a1("Sheet1", "B2", LiteralValue::Text("Twenty".into()));
942        let ctx = wb.interpreter();
943        let table = ASTNode::new(
944            ASTNodeType::Reference {
945                original: "A1:B2".into(),
946                reference: ReferenceType::Range {
947                    sheet: None,
948                    start_row: Some(1),
949                    start_col: Some(1),
950                    end_row: Some(2),
951                    end_col: Some(2),
952                },
953            },
954            None,
955        );
956        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
957        // Omit 4th arg: should be exact, so lookup 15 returns #N/A not row 1
958        let fifteen = lit(LiteralValue::Int(15));
959        let two = lit(LiteralValue::Int(2));
960        let args = vec![
961            ArgumentHandle::new(&fifteen, &ctx),
962            ArgumentHandle::new(&table, &ctx),
963            ArgumentHandle::new(&two, &ctx),
964        ];
965        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
966        assert!(matches!(v, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
967        // Exact match 20 works
968        let twenty = lit(LiteralValue::Int(20));
969        let args2 = vec![
970            ArgumentHandle::new(&twenty, &ctx),
971            ArgumentHandle::new(&table, &ctx),
972            ArgumentHandle::new(&two, &ctx),
973        ];
974        let v2 = f.dispatch(&args2, &ctx.function_context(None)).unwrap();
975        assert_eq!(v2, LiteralValue::Text("Twenty".into()));
976    }
977
978    #[test]
979    fn hlookup_basic() {
980        let wb = TestWorkbook::new()
981            .with_function(Arc::new(HLookupFn))
982            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Key1".into()))
983            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("Key2".into()))
984            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(100))
985            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200));
986        let ctx = wb.interpreter();
987        let table = ASTNode::new(
988            ASTNodeType::Reference {
989                original: "A1:B2".into(),
990                reference: ReferenceType::Range {
991                    sheet: None,
992                    start_row: Some(1),
993                    start_col: Some(1),
994                    end_row: Some(2),
995                    end_col: Some(2),
996                },
997            },
998            None,
999        );
1000        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
1001        let key1 = lit(LiteralValue::Text("Key1".into()));
1002        let two = lit(LiteralValue::Int(2));
1003        let false_lit = lit(LiteralValue::Boolean(false));
1004        let args = vec![
1005            ArgumentHandle::new(&key1, &ctx),
1006            ArgumentHandle::new(&table, &ctx),
1007            ArgumentHandle::new(&two, &ctx),
1008            ArgumentHandle::new(&false_lit, &ctx),
1009        ];
1010        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1011        assert_eq!(v, LiteralValue::Int(100));
1012    }
1013
1014    #[test]
1015    fn hlookup_default_exact_behavior() {
1016        let wb = TestWorkbook::new()
1017            .with_function(Arc::new(HLookupFn))
1018            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1019            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
1020            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Ten".into()))
1021            .with_cell_a1("Sheet1", "B2", LiteralValue::Text("Twenty".into()));
1022        let ctx = wb.interpreter();
1023        let table = ASTNode::new(
1024            ASTNodeType::Reference {
1025                original: "A1:B2".into(),
1026                reference: ReferenceType::Range {
1027                    sheet: None,
1028                    start_row: Some(1),
1029                    start_col: Some(1),
1030                    end_row: Some(2),
1031                    end_col: Some(2),
1032                },
1033            },
1034            None,
1035        );
1036        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
1037        // Omit 4th arg: exact expected, lookup 15 returns #N/A
1038        let fifteen = lit(LiteralValue::Int(15));
1039        let two = lit(LiteralValue::Int(2));
1040        let args = vec![
1041            ArgumentHandle::new(&fifteen, &ctx),
1042            ArgumentHandle::new(&table, &ctx),
1043            ArgumentHandle::new(&two, &ctx),
1044        ];
1045        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1046        assert!(matches!(v, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
1047        // Exact 20 works
1048        let twenty = lit(LiteralValue::Int(20));
1049        let args2 = vec![
1050            ArgumentHandle::new(&twenty, &ctx),
1051            ArgumentHandle::new(&table, &ctx),
1052            ArgumentHandle::new(&two, &ctx),
1053        ];
1054        let v2 = f.dispatch(&args2, &ctx.function_context(None)).unwrap();
1055        assert_eq!(v2, LiteralValue::Text("Twenty".into()));
1056    }
1057
1058    // ---------------- Additional Edge / Error Tests ----------------
1059
1060    #[test]
1061    fn match_not_found_exact_and_approx_low() {
1062        let wb = TestWorkbook::new()
1063            .with_function(Arc::new(MatchFn))
1064            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1065            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
1066            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30));
1067        let ctx = wb.interpreter();
1068        let range = ASTNode::new(
1069            ASTNodeType::Reference {
1070                original: "A1:A3".into(),
1071                reference: ReferenceType::Range {
1072                    sheet: None,
1073                    start_row: Some(1),
1074                    start_col: Some(1),
1075                    end_row: Some(3),
1076                    end_col: Some(1),
1077                },
1078            },
1079            None,
1080        );
1081        let f = ctx.context.get_function("", "MATCH").unwrap();
1082        // Exact lookup for 25 -> #N/A
1083        let needle_exact = lit(LiteralValue::Int(25));
1084        let zero = lit(LiteralValue::Int(0));
1085        let args_exact = vec![
1086            ArgumentHandle::new(&needle_exact, &ctx),
1087            ArgumentHandle::new(&range, &ctx),
1088            ArgumentHandle::new(&zero, &ctx),
1089        ];
1090        let v_exact = f
1091            .dispatch(&args_exact, &ctx.function_context(None))
1092            .unwrap();
1093        assert!(matches!(v_exact, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
1094        // Approximate (default) lookup for 5 (below first) -> #N/A
1095        let five = lit(LiteralValue::Int(5));
1096        let args_low = vec![
1097            ArgumentHandle::new(&five, &ctx),
1098            ArgumentHandle::new(&range, &ctx),
1099        ];
1100        let v_low = f.dispatch(&args_low, &ctx.function_context(None)).unwrap();
1101        assert!(matches!(v_low, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
1102    }
1103
1104    #[test]
1105    fn vlookup_col_index_out_of_range_and_exact_not_found() {
1106        let wb = TestWorkbook::new()
1107            .with_function(Arc::new(VLookupFn))
1108            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("A".into()))
1109            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("B".into()))
1110            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(1))
1111            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2));
1112        let ctx = wb.interpreter();
1113        let table = ASTNode::new(
1114            ASTNodeType::Reference {
1115                original: "A1:B2".into(),
1116                reference: ReferenceType::Range {
1117                    sheet: None,
1118                    start_row: Some(1),
1119                    start_col: Some(1),
1120                    end_row: Some(2),
1121                    end_col: Some(2),
1122                },
1123            },
1124            None,
1125        );
1126        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
1127        // col_index 3 (out of 2) -> #REF!
1128        let key_a = lit(LiteralValue::Text("A".into()));
1129        let three = lit(LiteralValue::Int(3));
1130        let false_lit = lit(LiteralValue::Boolean(false));
1131        let args_bad_col = vec![
1132            ArgumentHandle::new(&key_a, &ctx),
1133            ArgumentHandle::new(&table, &ctx),
1134            ArgumentHandle::new(&three, &ctx),
1135            ArgumentHandle::new(&false_lit, &ctx),
1136        ];
1137        let v_bad_col = f
1138            .dispatch(&args_bad_col, &ctx.function_context(None))
1139            .unwrap();
1140        assert!(matches!(v_bad_col, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Ref));
1141        // Exact not found -> #N/A
1142        let key_missing = lit(LiteralValue::Text("Z".into()));
1143        let two = lit(LiteralValue::Int(2));
1144        let args_not_found = vec![
1145            ArgumentHandle::new(&key_missing, &ctx),
1146            ArgumentHandle::new(&table, &ctx),
1147            ArgumentHandle::new(&two, &ctx),
1148            ArgumentHandle::new(&false_lit, &ctx),
1149        ];
1150        let v_nf = f
1151            .dispatch(&args_not_found, &ctx.function_context(None))
1152            .unwrap();
1153        assert!(matches!(v_nf, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
1154    }
1155
1156    #[test]
1157    fn hlookup_row_index_zero_and_arg_schema_type_error() {
1158        let wb = TestWorkbook::new()
1159            .with_function(Arc::new(HLookupFn))
1160            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("A".into()))
1161            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("B".into()))
1162            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(1))
1163            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2));
1164        let ctx = wb.interpreter();
1165        let table = ASTNode::new(
1166            ASTNodeType::Reference {
1167                original: "A1:B2".into(),
1168                reference: ReferenceType::Range {
1169                    sheet: None,
1170                    start_row: Some(1),
1171                    start_col: Some(1),
1172                    end_row: Some(2),
1173                    end_col: Some(2),
1174                },
1175            },
1176            None,
1177        );
1178        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
1179        // row_index 0 -> #VALUE!
1180        let key_a = lit(LiteralValue::Text("A".into()));
1181        let zero = lit(LiteralValue::Int(0));
1182        let false_lit = lit(LiteralValue::Boolean(false));
1183        let args_zero = vec![
1184            ArgumentHandle::new(&key_a, &ctx),
1185            ArgumentHandle::new(&table, &ctx),
1186            ArgumentHandle::new(&zero, &ctx),
1187            ArgumentHandle::new(&false_lit, &ctx),
1188        ];
1189        let v_zero = f.dispatch(&args_zero, &ctx.function_context(None)).unwrap();
1190        assert!(matches!(v_zero, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
1191        // Arg schema type error: supply logical where row_index expects number (NumberStrict coercion fails -> #VALUE!)
1192        let true_lit = lit(LiteralValue::Boolean(true));
1193        let args_type = vec![
1194            ArgumentHandle::new(&key_a, &ctx),
1195            ArgumentHandle::new(&table, &ctx),
1196            ArgumentHandle::new(&true_lit, &ctx),
1197        ];
1198        let v_type = f.dispatch(&args_type, &ctx.function_context(None)).unwrap();
1199        assert!(matches!(v_type, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Value));
1200    }
1201
1202    #[test]
1203    fn match_invalid_second_arg_not_range_triggers_ref_error() {
1204        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
1205        let ctx = wb.interpreter();
1206        let f = ctx.context.get_function("", "MATCH").unwrap();
1207        let scalar_lookup = lit(LiteralValue::Int(10));
1208        let scalar_array = lit(LiteralValue::Int(20)); // SHOULD be a range per schema
1209        let args = vec![
1210            ArgumentHandle::new(&scalar_lookup, &ctx),
1211            ArgumentHandle::new(&scalar_array, &ctx),
1212        ];
1213        let v = f.dispatch(&args, &ctx.function_context(None)).unwrap();
1214        assert!(matches!(v, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Ref));
1215    }
1216}
formualizer_eval/builtins/lookup/core.rs

formualizer_eval/builtins/lookup/
core.rs
