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

formualizer_eval/builtins/lookup/
core.rs
