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

formualizer_eval/builtins/lookup/
core.rs
