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                    let v = rv.get_cell(i, target_col_idx);
426                    // Excel treats a direct reference to an empty cell as 0.
427                    // VLOOKUP/HLOOKUP return the referenced cell value, so match Excel by
428                    // materializing Empty as numeric 0. (Empty text "" remains Text(""))
429                    let v = match v {
430                        LiteralValue::Empty => LiteralValue::Number(0.0),
431                        other => other,
432                    };
433                    Ok(crate::traits::CalcValue::Scalar(v))
434                }
435                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
436                    ExcelError::new(ExcelErrorKind::Na),
437                ))),
438            }
439        } else {
440            // Handle array literal
441            let v = args[1].value()?.into_literal();
442            let table: Vec<Vec<LiteralValue>> = match v {
443                LiteralValue::Array(rows) => rows,
444                other => vec![vec![other]],
445            };
446            if table.is_empty() {
447                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
448                    ExcelError::new(ExcelErrorKind::Na),
449                )));
450            }
451            let width = table.first().map(|r| r.len()).unwrap_or(0);
452            if col_index as usize > width {
453                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
454                    ExcelError::new(ExcelErrorKind::Ref),
455                )));
456            }
457
458            // First column values for lookup
459            let first_col: Vec<LiteralValue> =
460                table.iter().filter_map(|r| r.first().cloned()).collect();
461            let row_idx_opt = if !approximate {
462                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
463                find_exact_index(&first_col, &lookup_value, wildcard_mode)
464            } else {
465                binary_search_match(&first_col, &lookup_value, 1)
466            };
467
468            match row_idx_opt {
469                Some(i) => {
470                    let target_col_idx = (col_index - 1) as usize;
471                    let val = table
472                        .get(i)
473                        .and_then(|r| r.get(target_col_idx))
474                        .cloned()
475                        .unwrap_or(LiteralValue::Empty);
476                    let val = match val {
477                        LiteralValue::Empty => LiteralValue::Number(0.0),
478                        other => other,
479                    };
480                    Ok(crate::traits::CalcValue::Scalar(val))
481                }
482                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
483                    ExcelError::new(ExcelErrorKind::Na),
484                ))),
485            }
486        }
487    }
488}
489
490#[derive(Debug)]
491pub struct HLookupFn;
492impl Function for HLookupFn {
493    fn name(&self) -> &'static str {
494        "HLOOKUP"
495    }
496    fn min_args(&self) -> usize {
497        3
498    }
499    func_caps!(PURE, LOOKUP);
500    fn arg_schema(&self) -> &'static [ArgSchema] {
501        use once_cell::sync::Lazy;
502        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
503            vec![
504                // lookup_value
505                ArgSchema {
506                    kinds: smallvec::smallvec![ArgKind::Any],
507                    required: true,
508                    by_ref: false,
509                    shape: ShapeKind::Scalar,
510                    coercion: CoercionPolicy::None,
511                    max: None,
512                    repeating: None,
513                    default: None,
514                },
515                // table_array (accepts both references and array literals)
516                ArgSchema {
517                    kinds: smallvec::smallvec![ArgKind::Any],
518                    required: true,
519                    by_ref: false,
520                    shape: ShapeKind::Range,
521                    coercion: CoercionPolicy::None,
522                    max: None,
523                    repeating: None,
524                    default: None,
525                },
526                // row_index_num (strict number)
527                ArgSchema {
528                    kinds: smallvec::smallvec![ArgKind::Number],
529                    required: true,
530                    by_ref: false,
531                    shape: ShapeKind::Scalar,
532                    coercion: CoercionPolicy::NumberStrict,
533                    max: None,
534                    repeating: None,
535                    default: None,
536                },
537                // range_lookup (optional logical, default FALSE for safer exact default)
538                ArgSchema {
539                    kinds: smallvec::smallvec![ArgKind::Logical],
540                    required: false,
541                    by_ref: false,
542                    shape: ShapeKind::Scalar,
543                    coercion: CoercionPolicy::Logical,
544                    max: None,
545                    repeating: None,
546                    default: Some(LiteralValue::Boolean(false)),
547                },
548            ]
549        });
550        &SCHEMA
551    }
552    fn eval<'a, 'b, 'c>(
553        &self,
554        args: &'c [ArgumentHandle<'a, 'b>],
555        ctx: &dyn FunctionContext<'b>,
556    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
557        if args.len() < 3 {
558            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
559                ExcelError::new(ExcelErrorKind::Na),
560            )));
561        }
562        let lookup_value = args[0].value()?.into_literal();
563
564        // Try to get table as reference, fall back to array literal
565        let table_ref_opt = args[1].as_reference_or_eval().ok();
566        let row_index = match args[2].value()?.into_literal() {
567            LiteralValue::Int(i) => i,
568            LiteralValue::Number(n) => n as i64,
569            _ => {
570                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
571                    ExcelError::new(ExcelErrorKind::Value),
572                )));
573            }
574        };
575        if row_index < 1 {
576            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
577                ExcelError::new(ExcelErrorKind::Value),
578            )));
579        }
580        let approximate = if args.len() >= 4 {
581            match args[3].value()?.into_literal() {
582                LiteralValue::Boolean(b) => b,
583                _ => true,
584            }
585        } else {
586            false
587        };
588        // Handle both cell references and array literals
589        if let Some(table_ref) = table_ref_opt {
590            let (sheet, sr, sc, er, ec) = match &table_ref {
591                ReferenceType::Range {
592                    sheet,
593                    start_row: Some(sr),
594                    start_col: Some(sc),
595                    end_row: Some(er),
596                    end_col: Some(ec),
597                    ..
598                } => (sheet.clone(), *sr, *sc, *er, *ec),
599                _ => {
600                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
601                        ExcelError::new(ExcelErrorKind::Ref),
602                    )));
603                }
604            };
605            let current_sheet = ctx.current_sheet();
606            let sheet_name = sheet.as_deref().unwrap_or(current_sheet);
607            let height = er - sr + 1;
608            let width = ec - sc + 1;
609            if row_index as u32 > height {
610                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
611                    ExcelError::new(ExcelErrorKind::Ref),
612                )));
613            }
614            let rv = ctx.resolve_range_view(&table_ref, sheet_name)?;
615            let cols = rv.dims().1;
616            let first_row_view = rv.sub_view(0, 0, 1, cols);
617            let col_idx_opt = if approximate {
618                let mut first_row: Vec<LiteralValue> = Vec::with_capacity(width as usize);
619                first_row_view.for_each_row(&mut |row| {
620                    if first_row.is_empty() {
621                        first_row.extend_from_slice(row);
622                    }
623                    Ok(())
624                })?;
625                binary_search_match(&first_row, &lookup_value, 1)
626            } else {
627                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
628                super::lookup_utils::find_exact_index_in_view(
629                    &first_row_view,
630                    &lookup_value,
631                    wildcard_mode,
632                )?
633            };
634
635            match col_idx_opt {
636                Some(i) => {
637                    let target_row_idx = (row_index - 1) as usize;
638                    let v = rv.get_cell(target_row_idx, i);
639                    let v = match v {
640                        LiteralValue::Empty => LiteralValue::Number(0.0),
641                        other => other,
642                    };
643                    Ok(crate::traits::CalcValue::Scalar(v))
644                }
645                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
646                    ExcelError::new(ExcelErrorKind::Na),
647                ))),
648            }
649        } else {
650            // Handle array literal
651            let v = args[1].value()?.into_literal();
652            let table: Vec<Vec<LiteralValue>> = match v {
653                LiteralValue::Array(rows) => rows,
654                other => vec![vec![other]],
655            };
656            if table.is_empty() {
657                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
658                    ExcelError::new(ExcelErrorKind::Na),
659                )));
660            }
661            let height = table.len();
662            if row_index as usize > height {
663                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
664                    ExcelError::new(ExcelErrorKind::Ref),
665                )));
666            }
667
668            // First row values for lookup
669            let first_row: Vec<LiteralValue> = table.first().cloned().unwrap_or_default();
670            let col_idx_opt = if approximate {
671                binary_search_match(&first_row, &lookup_value, 1)
672            } else {
673                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
674                find_exact_index(&first_row, &lookup_value, wildcard_mode)
675            };
676
677            match col_idx_opt {
678                Some(i) => {
679                    let target_row_idx = (row_index - 1) as usize;
680                    let val = table
681                        .get(target_row_idx)
682                        .and_then(|r| r.get(i))
683                        .cloned()
684                        .unwrap_or(LiteralValue::Empty);
685                    let val = match val {
686                        LiteralValue::Empty => LiteralValue::Number(0.0),
687                        other => other,
688                    };
689                    Ok(crate::traits::CalcValue::Scalar(val))
690                }
691                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
692                    ExcelError::new(ExcelErrorKind::Na),
693                ))),
694            }
695        }
696    }
697}
698
699pub fn register_builtins() {
700    use crate::function_registry::register_function;
701    use std::sync::Arc;
702    register_function(Arc::new(MatchFn));
703    register_function(Arc::new(VLookupFn));
704    register_function(Arc::new(HLookupFn));
705}
706
707#[cfg(test)]
708mod tests {
709    use super::*;
710    use crate::test_workbook::TestWorkbook;
711    use crate::traits::ArgumentHandle;
712    use formualizer_parse::parser::{ASTNode, ASTNodeType, ReferenceType};
713    use std::sync::Arc;
714    fn lit(v: LiteralValue) -> ASTNode {
715        ASTNode::new(ASTNodeType::Literal(v), None)
716    }
717
718    #[test]
719    fn match_wildcard_and_descending_and_unsorted() {
720        // Wildcard: A1:A4 = "foo", "fob", "bar", "baz"
721        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
722        let wb = wb
723            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("foo".into()))
724            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("fob".into()))
725            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("bar".into()))
726            .with_cell_a1("Sheet1", "A4", LiteralValue::Text("baz".into()));
727        let ctx = wb.interpreter();
728        let range = ASTNode::new(
729            ASTNodeType::Reference {
730                original: "A1:A4".into(),
731                reference: ReferenceType::range(None, Some(1), Some(1), Some(4), Some(1)),
732            },
733            None,
734        );
735        let f = ctx.context.get_function("", "MATCH").unwrap();
736        // Wildcard *o* matches "foo" (1) and "fob" (2), should return first match (1)
737        let pat = lit(LiteralValue::Text("*o*".into()));
738        let zero = lit(LiteralValue::Int(0));
739        let args = vec![
740            ArgumentHandle::new(&pat, &ctx),
741            ArgumentHandle::new(&range, &ctx),
742            ArgumentHandle::new(&zero, &ctx),
743        ];
744        let v = f
745            .dispatch(&args, &ctx.function_context(None))
746            .unwrap()
747            .into_literal();
748        assert_eq!(v, LiteralValue::Int(1));
749        // Wildcard b?z matches "baz" (4)
750        let pat2 = lit(LiteralValue::Text("b?z".into()));
751        let args2 = vec![
752            ArgumentHandle::new(&pat2, &ctx),
753            ArgumentHandle::new(&range, &ctx),
754            ArgumentHandle::new(&zero, &ctx),
755        ];
756        let v2 = f
757            .dispatch(&args2, &ctx.function_context(None))
758            .unwrap()
759            .into_literal();
760        assert_eq!(v2, LiteralValue::Int(4));
761        // No match
762        let pat3 = lit(LiteralValue::Text("z*".into()));
763        let args3 = vec![
764            ArgumentHandle::new(&pat3, &ctx),
765            ArgumentHandle::new(&range, &ctx),
766            ArgumentHandle::new(&zero, &ctx),
767        ];
768        let v3 = f
769            .dispatch(&args3, &ctx.function_context(None))
770            .unwrap()
771            .into_literal();
772        assert!(matches!(v3, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
773
774        // Descending approximate: 50,40,30,20,10; match_type = -1
775        let wb2 = TestWorkbook::new()
776            .with_function(Arc::new(MatchFn))
777            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(50))
778            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(40))
779            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
780            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(20))
781            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(10));
782        let ctx2 = wb2.interpreter();
783        let range2 = ASTNode::new(
784            ASTNodeType::Reference {
785                original: "A1:A5".into(),
786                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
787            },
788            None,
789        );
790        let minus1 = lit(LiteralValue::Int(-1));
791        let thirty = lit(LiteralValue::Int(30));
792        let args_desc = vec![
793            ArgumentHandle::new(&thirty, &ctx2),
794            ArgumentHandle::new(&range2, &ctx2),
795            ArgumentHandle::new(&minus1, &ctx2),
796        ];
797        let v_desc = f
798            .dispatch(&args_desc, &ctx2.function_context(None))
799            .unwrap()
800            .into_literal();
801        assert_eq!(v_desc, LiteralValue::Int(3));
802        // Descending, not found (needle > max)
803        let sixty = lit(LiteralValue::Int(60));
804        let args_desc2 = vec![
805            ArgumentHandle::new(&sixty, &ctx2),
806            ArgumentHandle::new(&range2, &ctx2),
807            ArgumentHandle::new(&minus1, &ctx2),
808        ];
809        let v_desc2 = f
810            .dispatch(&args_desc2, &ctx2.function_context(None))
811            .unwrap()
812            .into_literal();
813        assert!(matches!(v_desc2, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
814
815        // Unsorted detection: 10, 30, 20, 40, 50 (not sorted ascending)
816        let wb3 = TestWorkbook::new()
817            .with_function(Arc::new(MatchFn))
818            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
819            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
820            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20))
821            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(40))
822            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(50));
823        let ctx3 = wb3.interpreter();
824        let range3 = ASTNode::new(
825            ASTNodeType::Reference {
826                original: "A1:A5".into(),
827                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
828            },
829            None,
830        );
831        let args_unsorted = vec![
832            ArgumentHandle::new(&thirty, &ctx3),
833            ArgumentHandle::new(&range3, &ctx3),
834        ];
835        let v_unsorted = f
836            .dispatch(&args_unsorted, &ctx3.function_context(None))
837            .unwrap()
838            .into_literal();
839        assert!(matches!(v_unsorted, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
840        // Unsorted detection descending: 50, 30, 40, 20, 10
841        let wb4 = TestWorkbook::new()
842            .with_function(Arc::new(MatchFn))
843            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(50))
844            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
845            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(40))
846            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(20))
847            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(10));
848        let ctx4 = wb4.interpreter();
849        let range4 = ASTNode::new(
850            ASTNodeType::Reference {
851                original: "A1:A5".into(),
852                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
853            },
854            None,
855        );
856        let args_unsorted_desc = vec![
857            ArgumentHandle::new(&thirty, &ctx4),
858            ArgumentHandle::new(&range4, &ctx4),
859            ArgumentHandle::new(&minus1, &ctx4),
860        ];
861        let v_unsorted_desc = f
862            .dispatch(&args_unsorted_desc, &ctx4.function_context(None))
863            .unwrap()
864            .into_literal();
865        assert!(matches!(v_unsorted_desc, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
866    }
867
868    #[test]
869    fn match_exact_and_approx() {
870        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
871        let wb = wb
872            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
873            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
874            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
875            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(40))
876            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(50));
877        let ctx = wb.interpreter();
878        let range = ASTNode::new(
879            ASTNodeType::Reference {
880                original: "A1:A5".into(),
881                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
882            },
883            None,
884        );
885        let f = ctx.context.get_function("", "MATCH").unwrap();
886        let thirty = lit(LiteralValue::Int(30));
887        let zero = lit(LiteralValue::Int(0));
888        let args = vec![
889            ArgumentHandle::new(&thirty, &ctx),
890            ArgumentHandle::new(&range, &ctx),
891            ArgumentHandle::new(&zero, &ctx),
892        ];
893        let v = f
894            .dispatch(&args, &ctx.function_context(None))
895            .unwrap()
896            .into_literal();
897        assert_eq!(v, LiteralValue::Int(3));
898        let thirty_seven = lit(LiteralValue::Int(37));
899        let args = vec![
900            ArgumentHandle::new(&thirty_seven, &ctx),
901            ArgumentHandle::new(&range, &ctx),
902        ];
903        let v = f
904            .dispatch(&args, &ctx.function_context(None))
905            .unwrap()
906            .into_literal();
907        assert_eq!(v, LiteralValue::Int(3));
908    }
909
910    #[test]
911    fn vlookup_basic() {
912        let wb = TestWorkbook::new()
913            .with_function(Arc::new(VLookupFn))
914            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Key1".into()))
915            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Key2".into()))
916            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(100))
917            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200));
918        let ctx = wb.interpreter();
919        let table = ASTNode::new(
920            ASTNodeType::Reference {
921                original: "A1:B2".into(),
922                reference: ReferenceType::range(None, Some(1), Some(1), Some(2), Some(2)),
923            },
924            None,
925        );
926        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
927        let key2 = lit(LiteralValue::Text("Key2".into()));
928        let two = lit(LiteralValue::Int(2));
929        let false_lit = lit(LiteralValue::Boolean(false));
930        let args = vec![
931            ArgumentHandle::new(&key2, &ctx),
932            ArgumentHandle::new(&table, &ctx),
933            ArgumentHandle::new(&two, &ctx),
934            ArgumentHandle::new(&false_lit, &ctx),
935        ];
936        let v = f
937            .dispatch(&args, &ctx.function_context(None))
938            .unwrap()
939            .into_literal();
940        assert_eq!(v, LiteralValue::Number(200.0));
941    }
942
943    #[test]
944    fn vlookup_blank_target_cell_returns_zero() {
945        // Excel treats a direct reference to an empty cell as 0.
946        // VLOOKUP should therefore return 0 (not Empty) when the found cell is empty.
947        let wb = TestWorkbook::new()
948            .with_function(Arc::new(VLookupFn))
949            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1));
950
951        let ctx = wb.interpreter();
952        let table = ASTNode::new(
953            ASTNodeType::Reference {
954                original: "A1:B1".into(),
955                reference: ReferenceType::range(None, Some(1), Some(1), Some(1), Some(2)),
956            },
957            None,
958        );
959        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
960        let key1 = lit(LiteralValue::Int(1));
961        let two = lit(LiteralValue::Int(2));
962        let false_lit = lit(LiteralValue::Boolean(false));
963        let args = vec![
964            ArgumentHandle::new(&key1, &ctx),
965            ArgumentHandle::new(&table, &ctx),
966            ArgumentHandle::new(&two, &ctx),
967            ArgumentHandle::new(&false_lit, &ctx),
968        ];
969        let v = f
970            .dispatch(&args, &ctx.function_context(None))
971            .unwrap()
972            .into_literal();
973        assert_eq!(v, LiteralValue::Number(0.0));
974    }
975
976    #[test]
977    fn hlookup_basic() {
978        let wb = TestWorkbook::new()
979            .with_function(Arc::new(HLookupFn))
980            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Key1".into()))
981            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("Key2".into()))
982            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(100))
983            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200));
984        let ctx = wb.interpreter();
985        let table = ASTNode::new(
986            ASTNodeType::Reference {
987                original: "A1:B2".into(),
988                reference: ReferenceType::range(None, Some(1), Some(1), Some(2), Some(2)),
989            },
990            None,
991        );
992        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
993        let key1 = lit(LiteralValue::Text("Key1".into()));
994        let two = lit(LiteralValue::Int(2));
995        let false_lit = lit(LiteralValue::Boolean(false));
996        let args = vec![
997            ArgumentHandle::new(&key1, &ctx),
998            ArgumentHandle::new(&table, &ctx),
999            ArgumentHandle::new(&two, &ctx),
1000            ArgumentHandle::new(&false_lit, &ctx),
1001        ];
1002        let v = f
1003            .dispatch(&args, &ctx.function_context(None))
1004            .unwrap()
1005            .into_literal();
1006        assert_eq!(v, LiteralValue::Number(100.0));
1007    }
1008
1009    #[test]
1010    fn hlookup_blank_target_cell_returns_zero() {
1011        let wb = TestWorkbook::new()
1012            .with_function(Arc::new(HLookupFn))
1013            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1));
1014
1015        let ctx = wb.interpreter();
1016        let table = ASTNode::new(
1017            ASTNodeType::Reference {
1018                original: "A1:B2".into(),
1019                reference: ReferenceType::range(None, Some(1), Some(1), Some(2), Some(2)),
1020            },
1021            None,
1022        );
1023        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
1024        let key1 = lit(LiteralValue::Int(1));
1025        let two = lit(LiteralValue::Int(2));
1026        let false_lit = lit(LiteralValue::Boolean(false));
1027        let args = vec![
1028            ArgumentHandle::new(&key1, &ctx),
1029            ArgumentHandle::new(&table, &ctx),
1030            ArgumentHandle::new(&two, &ctx),
1031            ArgumentHandle::new(&false_lit, &ctx),
1032        ];
1033        let v = f
1034            .dispatch(&args, &ctx.function_context(None))
1035            .unwrap()
1036            .into_literal();
1037        assert_eq!(v, LiteralValue::Number(0.0));
1038    }
1039}
formualizer_eval/builtins/lookup/core.rs

formualizer_eval/builtins/lookup/
core.rs
