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 (accepts both references and array literals)
89                ArgSchema {
90                    kinds: smallvec::smallvec![ArgKind::Any],
91                    required: true,
92                    by_ref: false,
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<'a, 'b, 'c>(
115        &self,
116        args: &'c [ArgumentHandle<'a, 'b>],
117        ctx: &dyn FunctionContext<'b>,
118    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
119        if args.len() < 2 {
120            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
121                ExcelError::new(ExcelErrorKind::Na),
122            )));
123        }
124        let cv = args[0].value()?;
125        let lookup_value = cv.into_literal();
126        if let LiteralValue::Error(e) = lookup_value {
127            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
128        }
129        let mut match_type = 1.0; // default
130        if args.len() >= 3 {
131            let mt_val = args[2].value()?.into_literal();
132            if let LiteralValue::Error(e) = mt_val {
133                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
134            }
135            match mt_val {
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        if let Some(r) = arr_ref {
155            let current_sheet = ctx.current_sheet();
156            match ctx.resolve_range_view(&r, current_sheet) {
157                Ok(rv) => {
158                    if mt == 0 {
159                        let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
160                        if let Some(idx) = super::lookup_utils::find_exact_index_in_view(
161                            &rv,
162                            &lookup_value,
163                            wildcard_mode,
164                        )? {
165                            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
166                                (idx + 1) as i64,
167                            )));
168                        }
169                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
170                            ExcelError::new(ExcelErrorKind::Na),
171                        )));
172                    }
173
174                    // Fallback for approximate match modes (handled via materialization for now)
175                    let mut values: Vec<LiteralValue> = Vec::new();
176                    if let Err(e) = rv.for_each_cell(&mut |v| {
177                        values.push(v.clone());
178                        Ok(())
179                    }) {
180                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
181                    }
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(crate::traits::CalcValue::Scalar(LiteralValue::Error(
195                            ExcelError::new(ExcelErrorKind::Na),
196                        )));
197                    }
198                    let idx = if values.len() < 8 {
199                        // linear small
200                        let mut best: Option<(usize, &LiteralValue)> = None;
201                        for (i, v) in values.iter().enumerate() {
202                            if let Some(c) = cmp_for_lookup(v, &lookup_value) {
203                                // compare candidate to needle
204                                if mt == 1 {
205                                    // v <= needle
206                                    if (c == 0 || c == -1)
207                                        && (best.is_none() || i > best.unwrap().0)
208                                    {
209                                        best = Some((i, v));
210                                    }
211                                } else {
212                                    // -1, v >= needle
213                                    if (c == 0 || c == 1) && (best.is_none() || i > best.unwrap().0)
214                                    {
215                                        best = Some((i, v));
216                                    }
217                                }
218                            }
219                        }
220                        best.map(|(i, _)| i)
221                    } else {
222                        binary_search_match(&values, &lookup_value, mt)
223                    };
224                    match idx {
225                        Some(i) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
226                            (i + 1) as i64,
227                        ))),
228                        None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
229                            ExcelError::new(ExcelErrorKind::Na),
230                        ))),
231                    }
232                }
233                Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
234            }
235        } else {
236            // Handle array literals and other non-reference values
237            let v = args[1].value()?.into_literal();
238            let values: Vec<LiteralValue> = match v {
239                LiteralValue::Array(rows) => {
240                    // Flatten the array (MATCH works on 1D, so take first row or column)
241                    if rows.len() == 1 {
242                        // Single row - use as-is
243                        rows.into_iter().next().unwrap_or_default()
244                    } else if rows.iter().all(|r| r.len() == 1) {
245                        // Column vector - extract first element of each row
246                        rows.into_iter()
247                            .filter_map(|r| r.into_iter().next())
248                            .collect()
249                    } else {
250                        // 2D array - flatten row by row
251                        rows.into_iter().flatten().collect()
252                    }
253                }
254                other => vec![other],
255            };
256            let idx = if mt == 0 {
257                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
258                find_exact_index(&values, &lookup_value, wildcard_mode)
259            } else {
260                binary_search_match(&values, &lookup_value, mt)
261            };
262            match idx {
263                Some(i) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
264                    (i + 1) as i64,
265                ))),
266                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
267                    ExcelError::new(ExcelErrorKind::Na),
268                ))),
269            }
270        }
271    }
272}
273
274#[derive(Debug)]
275pub struct VLookupFn;
276impl Function for VLookupFn {
277    fn name(&self) -> &'static str {
278        "VLOOKUP"
279    }
280    fn min_args(&self) -> usize {
281        3
282    }
283    func_caps!(PURE, LOOKUP);
284    fn arg_schema(&self) -> &'static [ArgSchema] {
285        use once_cell::sync::Lazy;
286        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
287            vec![
288                // lookup_value
289                ArgSchema {
290                    kinds: smallvec::smallvec![ArgKind::Any],
291                    required: true,
292                    by_ref: false,
293                    shape: ShapeKind::Scalar,
294                    coercion: CoercionPolicy::None,
295                    max: None,
296                    repeating: None,
297                    default: None,
298                },
299                // table_array (accepts both references and array literals)
300                ArgSchema {
301                    kinds: smallvec::smallvec![ArgKind::Any],
302                    required: true,
303                    by_ref: false,
304                    shape: ShapeKind::Range,
305                    coercion: CoercionPolicy::None,
306                    max: None,
307                    repeating: None,
308                    default: None,
309                },
310                // col_index_num (strict number)
311                ArgSchema {
312                    kinds: smallvec::smallvec![ArgKind::Number],
313                    required: true,
314                    by_ref: false,
315                    shape: ShapeKind::Scalar,
316                    coercion: CoercionPolicy::NumberStrict,
317                    max: None,
318                    repeating: None,
319                    default: None,
320                },
321                // range_lookup (optional logical, default FALSE for safer exact default)
322                ArgSchema {
323                    kinds: smallvec::smallvec![ArgKind::Logical],
324                    required: false,
325                    by_ref: false,
326                    shape: ShapeKind::Scalar,
327                    coercion: CoercionPolicy::Logical,
328                    max: None,
329                    repeating: None,
330                    default: Some(LiteralValue::Boolean(false)),
331                },
332            ]
333        });
334        &SCHEMA
335    }
336    fn eval<'a, 'b, 'c>(
337        &self,
338        args: &'c [ArgumentHandle<'a, 'b>],
339        ctx: &dyn FunctionContext<'b>,
340    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
341        if args.len() < 3 {
342            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
343                ExcelError::new(ExcelErrorKind::Na),
344            )));
345        }
346        let lookup_value = args[0].value()?.into_literal();
347
348        // Try to get table as reference, fall back to array literal
349        let table_ref_opt = args[1].as_reference_or_eval().ok();
350        let col_index = match args[2].value()?.into_literal() {
351            LiteralValue::Int(i) => i,
352            LiteralValue::Number(n) => n as i64,
353            _ => {
354                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
355                    ExcelError::new(ExcelErrorKind::Value),
356                )));
357            }
358        };
359        if col_index < 1 {
360            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
361                ExcelError::new(ExcelErrorKind::Value),
362            )));
363        }
364        let approximate = if args.len() >= 4 {
365            match args[3].value()?.into_literal() {
366                LiteralValue::Boolean(b) => b,
367                _ => true,
368            }
369        } else {
370            false // engine chooses FALSE default (exact) rather than Excel's historical TRUE to avoid silent approximate matches
371        };
372        // Handle both cell references and array literals
373        if let Some(table_ref) = table_ref_opt {
374            let (sheet, sr, sc, er, ec) = match &table_ref {
375                ReferenceType::Range {
376                    sheet,
377                    start_row: Some(sr),
378                    start_col: Some(sc),
379                    end_row: Some(er),
380                    end_col: Some(ec),
381                    ..
382                } => (sheet.clone(), *sr, *sc, *er, *ec),
383                _ => {
384                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
385                        ExcelError::new(ExcelErrorKind::Ref),
386                    )));
387                }
388            };
389            let current_sheet = ctx.current_sheet();
390            let sheet_name = sheet.as_deref().unwrap_or(current_sheet);
391            let width = ec - sc + 1;
392            if col_index as u32 > width {
393                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
394                    ExcelError::new(ExcelErrorKind::Ref),
395                )));
396            }
397
398            let rv = ctx.resolve_range_view(&table_ref, sheet_name)?;
399            let rows = rv.dims().0;
400            let first_col_view = rv.sub_view(0, 0, rows, 1);
401            let row_idx_opt = if !approximate {
402                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
403                super::lookup_utils::find_exact_index_in_view(
404                    &first_col_view,
405                    &lookup_value,
406                    wildcard_mode,
407                )?
408            } else {
409                // Fallback for approximate mode (requires materializing first column for now)
410                let mut first_col: Vec<LiteralValue> = Vec::new();
411                first_col_view.for_each_row(&mut |row| {
412                    first_col.push(row[0].clone());
413                    Ok(())
414                })?;
415                if first_col.is_empty() {
416                    None
417                } else {
418                    binary_search_match(&first_col, &lookup_value, 1)
419                }
420            };
421
422            match row_idx_opt {
423                Some(i) => {
424                    let target_col_idx = (col_index - 1) as usize;
425                    Ok(crate::traits::CalcValue::Scalar(
426                        rv.get_cell(i, target_col_idx),
427                    ))
428                }
429                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
430                    ExcelError::new(ExcelErrorKind::Na),
431                ))),
432            }
433        } else {
434            // Handle array literal
435            let v = args[1].value()?.into_literal();
436            let table: Vec<Vec<LiteralValue>> = match v {
437                LiteralValue::Array(rows) => rows,
438                other => vec![vec![other]],
439            };
440            if table.is_empty() {
441                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
442                    ExcelError::new(ExcelErrorKind::Na),
443                )));
444            }
445            let width = table.first().map(|r| r.len()).unwrap_or(0);
446            if col_index as usize > width {
447                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
448                    ExcelError::new(ExcelErrorKind::Ref),
449                )));
450            }
451
452            // First column values for lookup
453            let first_col: Vec<LiteralValue> =
454                table.iter().filter_map(|r| r.first().cloned()).collect();
455            let row_idx_opt = if !approximate {
456                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
457                find_exact_index(&first_col, &lookup_value, wildcard_mode)
458            } else {
459                binary_search_match(&first_col, &lookup_value, 1)
460            };
461
462            match row_idx_opt {
463                Some(i) => {
464                    let target_col_idx = (col_index - 1) as usize;
465                    let val = table
466                        .get(i)
467                        .and_then(|r| r.get(target_col_idx))
468                        .cloned()
469                        .unwrap_or(LiteralValue::Empty);
470                    Ok(crate::traits::CalcValue::Scalar(val))
471                }
472                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
473                    ExcelError::new(ExcelErrorKind::Na),
474                ))),
475            }
476        }
477    }
478}
479
480#[derive(Debug)]
481pub struct HLookupFn;
482impl Function for HLookupFn {
483    fn name(&self) -> &'static str {
484        "HLOOKUP"
485    }
486    fn min_args(&self) -> usize {
487        3
488    }
489    func_caps!(PURE, LOOKUP);
490    fn arg_schema(&self) -> &'static [ArgSchema] {
491        use once_cell::sync::Lazy;
492        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
493            vec![
494                // lookup_value
495                ArgSchema {
496                    kinds: smallvec::smallvec![ArgKind::Any],
497                    required: true,
498                    by_ref: false,
499                    shape: ShapeKind::Scalar,
500                    coercion: CoercionPolicy::None,
501                    max: None,
502                    repeating: None,
503                    default: None,
504                },
505                // table_array (accepts both references and array literals)
506                ArgSchema {
507                    kinds: smallvec::smallvec![ArgKind::Any],
508                    required: true,
509                    by_ref: false,
510                    shape: ShapeKind::Range,
511                    coercion: CoercionPolicy::None,
512                    max: None,
513                    repeating: None,
514                    default: None,
515                },
516                // row_index_num (strict number)
517                ArgSchema {
518                    kinds: smallvec::smallvec![ArgKind::Number],
519                    required: true,
520                    by_ref: false,
521                    shape: ShapeKind::Scalar,
522                    coercion: CoercionPolicy::NumberStrict,
523                    max: None,
524                    repeating: None,
525                    default: None,
526                },
527                // range_lookup (optional logical, default FALSE for safer exact default)
528                ArgSchema {
529                    kinds: smallvec::smallvec![ArgKind::Logical],
530                    required: false,
531                    by_ref: false,
532                    shape: ShapeKind::Scalar,
533                    coercion: CoercionPolicy::Logical,
534                    max: None,
535                    repeating: None,
536                    default: Some(LiteralValue::Boolean(false)),
537                },
538            ]
539        });
540        &SCHEMA
541    }
542    fn eval<'a, 'b, 'c>(
543        &self,
544        args: &'c [ArgumentHandle<'a, 'b>],
545        ctx: &dyn FunctionContext<'b>,
546    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
547        if args.len() < 3 {
548            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
549                ExcelError::new(ExcelErrorKind::Na),
550            )));
551        }
552        let lookup_value = args[0].value()?.into_literal();
553
554        // Try to get table as reference, fall back to array literal
555        let table_ref_opt = args[1].as_reference_or_eval().ok();
556        let row_index = match args[2].value()?.into_literal() {
557            LiteralValue::Int(i) => i,
558            LiteralValue::Number(n) => n as i64,
559            _ => {
560                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
561                    ExcelError::new(ExcelErrorKind::Value),
562                )));
563            }
564        };
565        if row_index < 1 {
566            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
567                ExcelError::new(ExcelErrorKind::Value),
568            )));
569        }
570        let approximate = if args.len() >= 4 {
571            match args[3].value()?.into_literal() {
572                LiteralValue::Boolean(b) => b,
573                _ => true,
574            }
575        } else {
576            false
577        };
578        // Handle both cell references and array literals
579        if let Some(table_ref) = table_ref_opt {
580            let (sheet, sr, sc, er, ec) = match &table_ref {
581                ReferenceType::Range {
582                    sheet,
583                    start_row: Some(sr),
584                    start_col: Some(sc),
585                    end_row: Some(er),
586                    end_col: Some(ec),
587                    ..
588                } => (sheet.clone(), *sr, *sc, *er, *ec),
589                _ => {
590                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
591                        ExcelError::new(ExcelErrorKind::Ref),
592                    )));
593                }
594            };
595            let current_sheet = ctx.current_sheet();
596            let sheet_name = sheet.as_deref().unwrap_or(current_sheet);
597            let height = er - sr + 1;
598            let width = ec - sc + 1;
599            if row_index as u32 > height {
600                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
601                    ExcelError::new(ExcelErrorKind::Ref),
602                )));
603            }
604            let rv = ctx.resolve_range_view(&table_ref, sheet_name)?;
605            let cols = rv.dims().1;
606            let first_row_view = rv.sub_view(0, 0, 1, cols);
607            let col_idx_opt = if approximate {
608                let mut first_row: Vec<LiteralValue> = Vec::with_capacity(width as usize);
609                first_row_view.for_each_row(&mut |row| {
610                    if first_row.is_empty() {
611                        first_row.extend_from_slice(row);
612                    }
613                    Ok(())
614                })?;
615                binary_search_match(&first_row, &lookup_value, 1)
616            } else {
617                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
618                super::lookup_utils::find_exact_index_in_view(
619                    &first_row_view,
620                    &lookup_value,
621                    wildcard_mode,
622                )?
623            };
624
625            match col_idx_opt {
626                Some(i) => {
627                    let target_row_idx = (row_index - 1) as usize;
628                    Ok(crate::traits::CalcValue::Scalar(
629                        rv.get_cell(target_row_idx, i),
630                    ))
631                }
632                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
633                    ExcelError::new(ExcelErrorKind::Na),
634                ))),
635            }
636        } else {
637            // Handle array literal
638            let v = args[1].value()?.into_literal();
639            let table: Vec<Vec<LiteralValue>> = match v {
640                LiteralValue::Array(rows) => rows,
641                other => vec![vec![other]],
642            };
643            if table.is_empty() {
644                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
645                    ExcelError::new(ExcelErrorKind::Na),
646                )));
647            }
648            let height = table.len();
649            if row_index as usize > height {
650                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
651                    ExcelError::new(ExcelErrorKind::Ref),
652                )));
653            }
654
655            // First row values for lookup
656            let first_row: Vec<LiteralValue> = table.first().cloned().unwrap_or_default();
657            let col_idx_opt = if approximate {
658                binary_search_match(&first_row, &lookup_value, 1)
659            } else {
660                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
661                find_exact_index(&first_row, &lookup_value, wildcard_mode)
662            };
663
664            match col_idx_opt {
665                Some(i) => {
666                    let target_row_idx = (row_index - 1) as usize;
667                    let val = table
668                        .get(target_row_idx)
669                        .and_then(|r| r.get(i))
670                        .cloned()
671                        .unwrap_or(LiteralValue::Empty);
672                    Ok(crate::traits::CalcValue::Scalar(val))
673                }
674                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
675                    ExcelError::new(ExcelErrorKind::Na),
676                ))),
677            }
678        }
679    }
680}
681
682pub fn register_builtins() {
683    use crate::function_registry::register_function;
684    use std::sync::Arc;
685    register_function(Arc::new(MatchFn));
686    register_function(Arc::new(VLookupFn));
687    register_function(Arc::new(HLookupFn));
688}
689
690#[cfg(test)]
691mod tests {
692    use super::*;
693    use crate::test_workbook::TestWorkbook;
694    use crate::traits::ArgumentHandle;
695    use formualizer_parse::parser::{ASTNode, ASTNodeType, ReferenceType};
696    use std::sync::Arc;
697    fn lit(v: LiteralValue) -> ASTNode {
698        ASTNode::new(ASTNodeType::Literal(v), None)
699    }
700
701    #[test]
702    fn match_wildcard_and_descending_and_unsorted() {
703        // Wildcard: A1:A4 = "foo", "fob", "bar", "baz"
704        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
705        let wb = wb
706            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("foo".into()))
707            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("fob".into()))
708            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("bar".into()))
709            .with_cell_a1("Sheet1", "A4", LiteralValue::Text("baz".into()));
710        let ctx = wb.interpreter();
711        let range = ASTNode::new(
712            ASTNodeType::Reference {
713                original: "A1:A4".into(),
714                reference: ReferenceType::range(None, Some(1), Some(1), Some(4), Some(1)),
715            },
716            None,
717        );
718        let f = ctx.context.get_function("", "MATCH").unwrap();
719        // Wildcard *o* matches "foo" (1) and "fob" (2), should return first match (1)
720        let pat = lit(LiteralValue::Text("*o*".into()));
721        let zero = lit(LiteralValue::Int(0));
722        let args = vec![
723            ArgumentHandle::new(&pat, &ctx),
724            ArgumentHandle::new(&range, &ctx),
725            ArgumentHandle::new(&zero, &ctx),
726        ];
727        let v = f
728            .dispatch(&args, &ctx.function_context(None))
729            .unwrap()
730            .into_literal();
731        assert_eq!(v, LiteralValue::Int(1));
732        // Wildcard b?z matches "baz" (4)
733        let pat2 = lit(LiteralValue::Text("b?z".into()));
734        let args2 = vec![
735            ArgumentHandle::new(&pat2, &ctx),
736            ArgumentHandle::new(&range, &ctx),
737            ArgumentHandle::new(&zero, &ctx),
738        ];
739        let v2 = f
740            .dispatch(&args2, &ctx.function_context(None))
741            .unwrap()
742            .into_literal();
743        assert_eq!(v2, LiteralValue::Int(4));
744        // No match
745        let pat3 = lit(LiteralValue::Text("z*".into()));
746        let args3 = vec![
747            ArgumentHandle::new(&pat3, &ctx),
748            ArgumentHandle::new(&range, &ctx),
749            ArgumentHandle::new(&zero, &ctx),
750        ];
751        let v3 = f
752            .dispatch(&args3, &ctx.function_context(None))
753            .unwrap()
754            .into_literal();
755        assert!(matches!(v3, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
756
757        // Descending approximate: 50,40,30,20,10; match_type = -1
758        let wb2 = TestWorkbook::new()
759            .with_function(Arc::new(MatchFn))
760            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(50))
761            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(40))
762            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
763            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(20))
764            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(10));
765        let ctx2 = wb2.interpreter();
766        let range2 = ASTNode::new(
767            ASTNodeType::Reference {
768                original: "A1:A5".into(),
769                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
770            },
771            None,
772        );
773        let minus1 = lit(LiteralValue::Int(-1));
774        let thirty = lit(LiteralValue::Int(30));
775        let args_desc = vec![
776            ArgumentHandle::new(&thirty, &ctx2),
777            ArgumentHandle::new(&range2, &ctx2),
778            ArgumentHandle::new(&minus1, &ctx2),
779        ];
780        let v_desc = f
781            .dispatch(&args_desc, &ctx2.function_context(None))
782            .unwrap()
783            .into_literal();
784        assert_eq!(v_desc, LiteralValue::Int(3));
785        // Descending, not found (needle > max)
786        let sixty = lit(LiteralValue::Int(60));
787        let args_desc2 = vec![
788            ArgumentHandle::new(&sixty, &ctx2),
789            ArgumentHandle::new(&range2, &ctx2),
790            ArgumentHandle::new(&minus1, &ctx2),
791        ];
792        let v_desc2 = f
793            .dispatch(&args_desc2, &ctx2.function_context(None))
794            .unwrap()
795            .into_literal();
796        assert!(matches!(v_desc2, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
797
798        // Unsorted detection: 10, 30, 20, 40, 50 (not sorted ascending)
799        let wb3 = TestWorkbook::new()
800            .with_function(Arc::new(MatchFn))
801            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
802            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
803            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20))
804            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(40))
805            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(50));
806        let ctx3 = wb3.interpreter();
807        let range3 = ASTNode::new(
808            ASTNodeType::Reference {
809                original: "A1:A5".into(),
810                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
811            },
812            None,
813        );
814        let args_unsorted = vec![
815            ArgumentHandle::new(&thirty, &ctx3),
816            ArgumentHandle::new(&range3, &ctx3),
817        ];
818        let v_unsorted = f
819            .dispatch(&args_unsorted, &ctx3.function_context(None))
820            .unwrap()
821            .into_literal();
822        assert!(matches!(v_unsorted, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
823        // Unsorted detection descending: 50, 30, 40, 20, 10
824        let wb4 = TestWorkbook::new()
825            .with_function(Arc::new(MatchFn))
826            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(50))
827            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
828            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(40))
829            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(20))
830            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(10));
831        let ctx4 = wb4.interpreter();
832        let range4 = ASTNode::new(
833            ASTNodeType::Reference {
834                original: "A1:A5".into(),
835                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
836            },
837            None,
838        );
839        let args_unsorted_desc = vec![
840            ArgumentHandle::new(&thirty, &ctx4),
841            ArgumentHandle::new(&range4, &ctx4),
842            ArgumentHandle::new(&minus1, &ctx4),
843        ];
844        let v_unsorted_desc = f
845            .dispatch(&args_unsorted_desc, &ctx4.function_context(None))
846            .unwrap()
847            .into_literal();
848        assert!(matches!(v_unsorted_desc, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
849    }
850
851    #[test]
852    fn match_exact_and_approx() {
853        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
854        let wb = wb
855            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
856            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
857            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
858            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(40))
859            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(50));
860        let ctx = wb.interpreter();
861        let range = ASTNode::new(
862            ASTNodeType::Reference {
863                original: "A1:A5".into(),
864                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
865            },
866            None,
867        );
868        let f = ctx.context.get_function("", "MATCH").unwrap();
869        let thirty = lit(LiteralValue::Int(30));
870        let zero = lit(LiteralValue::Int(0));
871        let args = vec![
872            ArgumentHandle::new(&thirty, &ctx),
873            ArgumentHandle::new(&range, &ctx),
874            ArgumentHandle::new(&zero, &ctx),
875        ];
876        let v = f
877            .dispatch(&args, &ctx.function_context(None))
878            .unwrap()
879            .into_literal();
880        assert_eq!(v, LiteralValue::Int(3));
881        let thirty_seven = lit(LiteralValue::Int(37));
882        let args = vec![
883            ArgumentHandle::new(&thirty_seven, &ctx),
884            ArgumentHandle::new(&range, &ctx),
885        ];
886        let v = f
887            .dispatch(&args, &ctx.function_context(None))
888            .unwrap()
889            .into_literal();
890        assert_eq!(v, LiteralValue::Int(3));
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(None, Some(1), Some(1), Some(2), Some(2)),
906            },
907            None,
908        );
909        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
910        let key2 = lit(LiteralValue::Text("Key2".into()));
911        let two = lit(LiteralValue::Int(2));
912        let false_lit = lit(LiteralValue::Boolean(false));
913        let args = vec![
914            ArgumentHandle::new(&key2, &ctx),
915            ArgumentHandle::new(&table, &ctx),
916            ArgumentHandle::new(&two, &ctx),
917            ArgumentHandle::new(&false_lit, &ctx),
918        ];
919        let v = f
920            .dispatch(&args, &ctx.function_context(None))
921            .unwrap()
922            .into_literal();
923        assert_eq!(v, LiteralValue::Number(200.0));
924    }
925
926    #[test]
927    fn hlookup_basic() {
928        let wb = TestWorkbook::new()
929            .with_function(Arc::new(HLookupFn))
930            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Key1".into()))
931            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("Key2".into()))
932            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(100))
933            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200));
934        let ctx = wb.interpreter();
935        let table = ASTNode::new(
936            ASTNodeType::Reference {
937                original: "A1:B2".into(),
938                reference: ReferenceType::range(None, Some(1), Some(1), Some(2), Some(2)),
939            },
940            None,
941        );
942        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
943        let key1 = lit(LiteralValue::Text("Key1".into()));
944        let two = lit(LiteralValue::Int(2));
945        let false_lit = lit(LiteralValue::Boolean(false));
946        let args = vec![
947            ArgumentHandle::new(&key1, &ctx),
948            ArgumentHandle::new(&table, &ctx),
949            ArgumentHandle::new(&two, &ctx),
950            ArgumentHandle::new(&false_lit, &ctx),
951        ];
952        let v = f
953            .dispatch(&args, &ctx.function_context(None))
954            .unwrap()
955            .into_literal();
956        assert_eq!(v, LiteralValue::Number(100.0));
957    }
958}
formualizer_eval/builtins/lookup/core.rs

formualizer_eval/builtins/lookup/
core.rs
