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::engine::lookup_index_cache::LookupAxis;
15use crate::function::Function;
16use crate::traits::{ArgumentHandle, FunctionContext};
17use formualizer_common::ArgKind;
18use formualizer_common::{ExcelError, ExcelErrorKind, LiteralValue};
19use formualizer_macros::func_caps;
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;
65/// Returns the relative position of a lookup value in a one-dimensional array.
66///
67/// `MATCH` supports exact and approximate modes and returns a 1-based position.
68///
69/// # Remarks
70/// - `match_type` defaults to `1` (approximate, ascending).
71/// - `match_type=0` performs exact matching and supports `*`, `?`, and `~` wildcards for text.
72/// - `match_type=1` looks for the largest value less than or equal to the lookup value.
73/// - `match_type=-1` looks for the smallest value greater than or equal to the lookup value.
74/// - Approximate modes require sorted data; unsorted data returns `#N/A`.
75/// - If no match is found, returns `#N/A`.
76///
77/// # Examples
78/// ```yaml,sandbox
79/// title: "Exact text match"
80/// grid:
81///   A1: "A"
82///   A2: "B"
83///   A3: "C"
84/// formula: '=MATCH("B",A1:A3,0)'
85/// expected: 2
86/// ```
87///
88/// ```yaml,sandbox
89/// title: "Approximate numeric match"
90/// grid:
91///   A1: 10
92///   A2: 20
93///   A3: 30
94///   A4: 40
95/// formula: '=MATCH(27,A1:A4,1)'
96/// expected: 2
97/// ```
98///
99/// ```yaml,docs
100/// related:
101///   - XMATCH
102///   - XLOOKUP
103///   - VLOOKUP
104/// faq:
105///   - q: "Why does MATCH with match_type 1 or -1 return #N/A on unsorted data?"
106///     a: "Approximate modes assume ordered lookup data; this implementation treats detected unsorted inputs as no valid match and returns #N/A."
107///   - q: "When are wildcards interpreted in MATCH?"
108///     a: "Wildcard patterns (*, ?, ~ escapes) are only applied in exact mode (match_type=0) for text lookup values."
109/// ```
110/// [formualizer-docgen:schema:start]
111/// Name: MATCH
112/// Type: MatchFn
113/// Min args: 2
114/// Max args: 3
115/// Variadic: false
116/// Signature: MATCH(arg1: any@scalar, arg2: any@range, arg3?: number@scalar)
117/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=any,required=true,shape=range,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg3{kinds=number,required=false,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=true}
118/// Caps: PURE, LOOKUP
119/// [formualizer-docgen:schema:end]
120impl Function for MatchFn {
121    fn name(&self) -> &'static str {
122        "MATCH"
123    }
124    fn min_args(&self) -> usize {
125        2
126    }
127    func_caps!(PURE, LOOKUP);
128    fn arg_schema(&self) -> &'static [ArgSchema] {
129        use once_cell::sync::Lazy;
130        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
131            vec![
132                // lookup_value (any scalar)
133                ArgSchema {
134                    kinds: smallvec::smallvec![ArgKind::Any],
135                    required: true,
136                    by_ref: false,
137                    shape: ShapeKind::Scalar,
138                    coercion: CoercionPolicy::None,
139                    max: None,
140                    repeating: None,
141                    default: None,
142                },
143                // lookup_array (accepts both references and array literals)
144                ArgSchema {
145                    kinds: smallvec::smallvec![ArgKind::Any],
146                    required: true,
147                    by_ref: false,
148                    shape: ShapeKind::Range,
149                    coercion: CoercionPolicy::None,
150                    max: None,
151                    repeating: None,
152                    default: None,
153                },
154                // match_type (optional numeric, default 1)
155                ArgSchema {
156                    kinds: smallvec::smallvec![ArgKind::Number],
157                    required: false,
158                    by_ref: false,
159                    shape: ShapeKind::Scalar,
160                    coercion: CoercionPolicy::NumberLenientText,
161                    max: None,
162                    repeating: None,
163                    default: Some(LiteralValue::Number(1.0)),
164                },
165            ]
166        });
167        &SCHEMA
168    }
169    fn eval<'a, 'b, 'c>(
170        &self,
171        args: &'c [ArgumentHandle<'a, 'b>],
172        ctx: &dyn FunctionContext<'b>,
173    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
174        if args.len() < 2 {
175            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
176                ExcelError::new(ExcelErrorKind::Na),
177            )));
178        }
179        let cv = args[0].value()?;
180        let lookup_value = cv.into_literal();
181        if let LiteralValue::Error(e) = lookup_value {
182            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
183        }
184        let mut match_type = 1.0; // default
185        if args.len() >= 3 {
186            let mt_val = args[2].value()?.into_literal();
187            if let LiteralValue::Error(e) = mt_val {
188                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
189            }
190            match mt_val {
191                LiteralValue::Number(n) => match_type = n,
192                LiteralValue::Int(i) => match_type = i as f64,
193                LiteralValue::Text(s) => {
194                    if let Ok(n) = s.parse::<f64>() {
195                        match_type = n;
196                    }
197                }
198                _ => {}
199            }
200        }
201        let mt = if match_type > 0.0 {
202            1
203        } else if match_type < 0.0 {
204            -1
205        } else {
206            0
207        };
208        let arr_ref = args[1].as_reference_or_eval().ok();
209        if let Some(r) = arr_ref {
210            let current_sheet = ctx.current_sheet();
211            match ctx.resolve_range_view(&r, current_sheet) {
212                Ok(rv) => {
213                    if mt == 0 {
214                        let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
215                        if !wildcard_mode {
216                            let axis = if rv.dims().1 == 1 {
217                                Some(LookupAxis::ColumnInView(0))
218                            } else if rv.dims().0 == 1 {
219                                Some(LookupAxis::RowInView(0))
220                            } else {
221                                None
222                            };
223                            if let Some(axis) = axis
224                                && let Some(index) = ctx.get_lookup_index(&rv, axis)
225                            {
226                                if let Some(idx) = index.find_first_exact(&lookup_value) {
227                                    return Ok(crate::traits::CalcValue::Scalar(
228                                        LiteralValue::Int((idx + 1) as i64),
229                                    ));
230                                }
231                                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
232                                    ExcelError::new(ExcelErrorKind::Na),
233                                )));
234                            }
235                        }
236                        if let Some(idx) = super::lookup_utils::find_exact_index_in_view(
237                            &rv,
238                            &lookup_value,
239                            wildcard_mode,
240                        )? {
241                            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
242                                (idx + 1) as i64,
243                            )));
244                        }
245                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
246                            ExcelError::new(ExcelErrorKind::Na),
247                        )));
248                    }
249
250                    // Fallback for approximate match modes (handled via materialization for now)
251                    let mut values: Vec<LiteralValue> = Vec::new();
252                    if let Err(e) = rv.for_each_cell(&mut |v| {
253                        values.push(v.clone());
254                        Ok(())
255                    }) {
256                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e)));
257                    }
258
259                    // Lightweight unsorted detection for approximate modes
260                    let is_sorted = if mt == 1 {
261                        is_sorted_ascending(&values)
262                    } else if mt == -1 {
263                        values
264                            .windows(2)
265                            .all(|w| cmp_for_lookup(&w[0], &w[1]).is_some_and(|c| c >= 0))
266                    } else {
267                        true
268                    };
269                    if !is_sorted {
270                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
271                            ExcelError::new(ExcelErrorKind::Na),
272                        )));
273                    }
274                    let idx = if values.len() < 8 {
275                        // linear small
276                        let mut best: Option<(usize, &LiteralValue)> = None;
277                        for (i, v) in values.iter().enumerate() {
278                            if let Some(c) = cmp_for_lookup(v, &lookup_value) {
279                                // compare candidate to needle
280                                if mt == 1 {
281                                    // v <= needle
282                                    if (c == 0 || c == -1)
283                                        && (best.is_none() || i > best.unwrap().0)
284                                    {
285                                        best = Some((i, v));
286                                    }
287                                } else {
288                                    // -1, v >= needle
289                                    if (c == 0 || c == 1) && (best.is_none() || i > best.unwrap().0)
290                                    {
291                                        best = Some((i, v));
292                                    }
293                                }
294                            }
295                        }
296                        best.map(|(i, _)| i)
297                    } else {
298                        binary_search_match(&values, &lookup_value, mt)
299                    };
300                    match idx {
301                        Some(i) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
302                            (i + 1) as i64,
303                        ))),
304                        None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
305                            ExcelError::new(ExcelErrorKind::Na),
306                        ))),
307                    }
308                }
309                Err(e) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
310            }
311        } else {
312            // Handle array literals and other non-reference values
313            let v = args[1].value()?.into_literal();
314            let values: Vec<LiteralValue> = match v {
315                LiteralValue::Array(rows) => {
316                    // Flatten the array (MATCH works on 1D, so take first row or column)
317                    if rows.len() == 1 {
318                        // Single row - use as-is
319                        rows.into_iter().next().unwrap_or_default()
320                    } else if rows.iter().all(|r| r.len() == 1) {
321                        // Column vector - extract first element of each row
322                        rows.into_iter()
323                            .filter_map(|r| r.into_iter().next())
324                            .collect()
325                    } else {
326                        // 2D array - flatten row by row
327                        rows.into_iter().flatten().collect()
328                    }
329                }
330                other => vec![other],
331            };
332            let idx = if mt == 0 {
333                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
334                find_exact_index(&values, &lookup_value, wildcard_mode)
335            } else {
336                binary_search_match(&values, &lookup_value, mt)
337            };
338            match idx {
339                Some(i) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
340                    (i + 1) as i64,
341                ))),
342                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
343                    ExcelError::new(ExcelErrorKind::Na),
344                ))),
345            }
346        }
347    }
348}
349
350#[derive(Debug)]
351pub struct VLookupFn;
352/// Looks up a value in the first column of a table and returns a value from another column.
353///
354/// `VLOOKUP` searches vertically and returns the matching row's value from `col_index_num`.
355///
356/// # Remarks
357/// - `col_index_num` is 1-based and must be within the table width.
358/// - `range_lookup` defaults to `FALSE` in this engine (exact match by default).
359/// - When `range_lookup=TRUE`, approximate match logic is used against the first column.
360/// - If the lookup value is not found, returns `#N/A`.
361/// - If `col_index_num` is invalid, returns `#REF!` (or `#VALUE!` if non-numeric).
362/// - A matched empty target cell is materialized as numeric `0`.
363///
364/// # Examples
365/// ```yaml,sandbox
366/// title: "Exact match in a key/value table"
367/// grid:
368///   A1: "SKU-1"
369///   B1: 12.5
370///   A2: "SKU-2"
371///   B2: 18
372/// formula: '=VLOOKUP("SKU-2",A1:B2,2,FALSE)'
373/// expected: 18
374/// ```
375///
376/// ```yaml,sandbox
377/// title: "Approximate tier lookup"
378/// grid:
379///   A1: 0
380///   B1: "Bronze"
381///   A2: 1000
382///   B2: "Silver"
383///   A3: 5000
384///   B3: "Gold"
385/// formula: '=VLOOKUP(3200,A1:B3,2,TRUE)'
386/// expected: "Silver"
387/// ```
388///
389/// ```yaml,docs
390/// related:
391///   - HLOOKUP
392///   - XLOOKUP
393///   - MATCH
394/// faq:
395///   - q: "What is the default behavior when range_lookup is omitted?"
396///     a: "This engine defaults range_lookup to FALSE, so VLOOKUP performs exact matching unless TRUE is explicitly provided."
397///   - q: "What happens if col_index_num points outside the table?"
398///     a: "A numeric out-of-range column index returns #REF!, while a non-numeric col_index_num returns #VALUE!."
399/// ```
400/// [formualizer-docgen:schema:start]
401/// Name: VLOOKUP
402/// Type: VLookupFn
403/// Min args: 3
404/// Max args: 4
405/// Variadic: false
406/// Signature: VLOOKUP(arg1: any@scalar, arg2: any@range, arg3: number@scalar, arg4?: logical@scalar)
407/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=any,required=true,shape=range,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberStrict,max=None,repeating=None,default=false}; arg4{kinds=logical,required=false,shape=scalar,by_ref=false,coercion=Logical,max=None,repeating=None,default=true}
408/// Caps: PURE, LOOKUP
409/// [formualizer-docgen:schema:end]
410impl Function for VLookupFn {
411    fn name(&self) -> &'static str {
412        "VLOOKUP"
413    }
414    fn min_args(&self) -> usize {
415        3
416    }
417    func_caps!(PURE, LOOKUP);
418    fn arg_schema(&self) -> &'static [ArgSchema] {
419        use once_cell::sync::Lazy;
420        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
421            vec![
422                // lookup_value
423                ArgSchema {
424                    kinds: smallvec::smallvec![ArgKind::Any],
425                    required: true,
426                    by_ref: false,
427                    shape: ShapeKind::Scalar,
428                    coercion: CoercionPolicy::None,
429                    max: None,
430                    repeating: None,
431                    default: None,
432                },
433                // table_array (accepts both references and array literals)
434                ArgSchema {
435                    kinds: smallvec::smallvec![ArgKind::Any],
436                    required: true,
437                    by_ref: false,
438                    shape: ShapeKind::Range,
439                    coercion: CoercionPolicy::None,
440                    max: None,
441                    repeating: None,
442                    default: None,
443                },
444                // col_index_num (strict number)
445                ArgSchema {
446                    kinds: smallvec::smallvec![ArgKind::Number],
447                    required: true,
448                    by_ref: false,
449                    shape: ShapeKind::Scalar,
450                    coercion: CoercionPolicy::NumberStrict,
451                    max: None,
452                    repeating: None,
453                    default: None,
454                },
455                // range_lookup (optional logical, default FALSE for safer exact default)
456                ArgSchema {
457                    kinds: smallvec::smallvec![ArgKind::Logical],
458                    required: false,
459                    by_ref: false,
460                    shape: ShapeKind::Scalar,
461                    coercion: CoercionPolicy::Logical,
462                    max: None,
463                    repeating: None,
464                    default: Some(LiteralValue::Boolean(false)),
465                },
466            ]
467        });
468        &SCHEMA
469    }
470    fn eval<'a, 'b, 'c>(
471        &self,
472        args: &'c [ArgumentHandle<'a, 'b>],
473        ctx: &dyn FunctionContext<'b>,
474    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
475        if args.len() < 3 {
476            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
477                ExcelError::new(ExcelErrorKind::Na),
478            )));
479        }
480        let lookup_value = args[0].value()?.into_literal();
481
482        // Try to get table as reference, fall back to array literal
483        let table_ref_opt = args[1].as_reference_or_eval().ok();
484        let col_index = match args[2].value()?.into_literal() {
485            LiteralValue::Int(i) => i,
486            LiteralValue::Number(n) => n as i64,
487            _ => {
488                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
489                    ExcelError::new(ExcelErrorKind::Value),
490                )));
491            }
492        };
493        if col_index < 1 {
494            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
495                ExcelError::new(ExcelErrorKind::Value),
496            )));
497        }
498        let approximate = if args.len() >= 4 {
499            match args[3].value()?.into_literal() {
500                LiteralValue::Boolean(b) => b,
501                _ => true,
502            }
503        } else {
504            false // engine chooses FALSE default (exact) rather than Excel's historical TRUE to avoid silent approximate matches
505        };
506        // Handle both cell references and array literals
507        if let Some(table_ref) = table_ref_opt {
508            let current_sheet = ctx.current_sheet();
509            let rv = ctx.resolve_range_view(&table_ref, current_sheet)?;
510            let (rows, cols) = rv.dims();
511            if col_index as usize > cols {
512                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
513                    ExcelError::new(ExcelErrorKind::Ref),
514                )));
515            }
516
517            let first_col_view = rv.sub_view(0, 0, rows, 1);
518            let row_idx_opt = if !approximate {
519                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
520                if !wildcard_mode
521                    && let Some(index) = ctx.get_lookup_index(&rv, LookupAxis::ColumnInView(0))
522                {
523                    index.find_first_exact(&lookup_value)
524                } else {
525                    super::lookup_utils::find_exact_index_in_view(
526                        &first_col_view,
527                        &lookup_value,
528                        wildcard_mode,
529                    )?
530                }
531            } else {
532                // Fallback for approximate mode (requires materializing first column for now)
533                let mut first_col: Vec<LiteralValue> = Vec::new();
534                first_col_view.for_each_row(&mut |row| {
535                    first_col.push(row[0].clone());
536                    Ok(())
537                })?;
538                if first_col.is_empty() {
539                    None
540                } else {
541                    binary_search_match(&first_col, &lookup_value, 1)
542                }
543            };
544
545            match row_idx_opt {
546                Some(i) => {
547                    let target_col_idx = (col_index - 1) as usize;
548                    let v = rv.get_cell(i, target_col_idx);
549                    // Excel treats a direct reference to an empty cell as 0.
550                    // VLOOKUP/HLOOKUP return the referenced cell value, so match Excel by
551                    // materializing Empty as numeric 0. (Empty text "" remains Text(""))
552                    let v = match v {
553                        LiteralValue::Empty => LiteralValue::Number(0.0),
554                        other => other,
555                    };
556                    Ok(crate::traits::CalcValue::Scalar(v))
557                }
558                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
559                    ExcelError::new(ExcelErrorKind::Na),
560                ))),
561            }
562        } else {
563            // Handle array literal
564            let v = args[1].value()?.into_literal();
565            let table: Vec<Vec<LiteralValue>> = match v {
566                LiteralValue::Array(rows) => rows,
567                other => vec![vec![other]],
568            };
569            if table.is_empty() {
570                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
571                    ExcelError::new(ExcelErrorKind::Na),
572                )));
573            }
574            let width = table.first().map(|r| r.len()).unwrap_or(0);
575            if col_index as usize > width {
576                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
577                    ExcelError::new(ExcelErrorKind::Ref),
578                )));
579            }
580
581            // First column values for lookup
582            let first_col: Vec<LiteralValue> =
583                table.iter().filter_map(|r| r.first().cloned()).collect();
584            let row_idx_opt = if !approximate {
585                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
586                find_exact_index(&first_col, &lookup_value, wildcard_mode)
587            } else {
588                binary_search_match(&first_col, &lookup_value, 1)
589            };
590
591            match row_idx_opt {
592                Some(i) => {
593                    let target_col_idx = (col_index - 1) as usize;
594                    let val = table
595                        .get(i)
596                        .and_then(|r| r.get(target_col_idx))
597                        .cloned()
598                        .unwrap_or(LiteralValue::Empty);
599                    let val = match val {
600                        LiteralValue::Empty => LiteralValue::Number(0.0),
601                        other => other,
602                    };
603                    Ok(crate::traits::CalcValue::Scalar(val))
604                }
605                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
606                    ExcelError::new(ExcelErrorKind::Na),
607                ))),
608            }
609        }
610    }
611}
612
613#[derive(Debug)]
614pub struct HLookupFn;
615/// Looks up a value in the first row of a table and returns a value from another row.
616///
617/// `HLOOKUP` searches horizontally and returns the matching column's value from `row_index_num`.
618///
619/// # Remarks
620/// - `row_index_num` is 1-based and must be within the table height.
621/// - `range_lookup` defaults to `FALSE` in this engine (exact match by default).
622/// - When `range_lookup=TRUE`, approximate match logic is used against the first row.
623/// - If the lookup value is not found, returns `#N/A`.
624/// - If `row_index_num` is invalid, returns `#REF!` (or `#VALUE!` if non-numeric).
625/// - A matched empty target cell is materialized as numeric `0`.
626///
627/// # Examples
628/// ```yaml,sandbox
629/// title: "Exact match across header row"
630/// grid:
631///   A1: "Jan"
632///   B1: "Feb"
633///   A2: 120
634///   B2: 150
635/// formula: '=HLOOKUP("Feb",A1:B2,2,FALSE)'
636/// expected: 150
637/// ```
638///
639/// ```yaml,sandbox
640/// title: "Approximate threshold lookup"
641/// grid:
642///   A1: 0
643///   B1: 50
644///   C1: 80
645///   A2: "F"
646///   B2: "C"
647///   C2: "A"
648/// formula: '=HLOOKUP(72,A1:C2,2,TRUE)'
649/// expected: "C"
650/// ```
651///
652/// ```yaml,docs
653/// related:
654///   - VLOOKUP
655///   - XLOOKUP
656///   - MATCH
657/// faq:
658///   - q: "Does HLOOKUP default to exact or approximate matching?"
659///     a: "It defaults to exact matching in this engine because range_lookup defaults to FALSE."
660///   - q: "How are invalid row_index_num values reported?"
661///     a: "If row_index_num is outside table height HLOOKUP returns #REF!; if it is non-numeric it returns #VALUE!."
662/// ```
663/// [formualizer-docgen:schema:start]
664/// Name: HLOOKUP
665/// Type: HLookupFn
666/// Min args: 3
667/// Max args: 4
668/// Variadic: false
669/// Signature: HLOOKUP(arg1: any@scalar, arg2: any@range, arg3: number@scalar, arg4?: logical@scalar)
670/// Arg schema: arg1{kinds=any,required=true,shape=scalar,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg2{kinds=any,required=true,shape=range,by_ref=false,coercion=None,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberStrict,max=None,repeating=None,default=false}; arg4{kinds=logical,required=false,shape=scalar,by_ref=false,coercion=Logical,max=None,repeating=None,default=true}
671/// Caps: PURE, LOOKUP
672/// [formualizer-docgen:schema:end]
673impl Function for HLookupFn {
674    fn name(&self) -> &'static str {
675        "HLOOKUP"
676    }
677    fn min_args(&self) -> usize {
678        3
679    }
680    func_caps!(PURE, LOOKUP);
681    fn arg_schema(&self) -> &'static [ArgSchema] {
682        use once_cell::sync::Lazy;
683        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
684            vec![
685                // lookup_value
686                ArgSchema {
687                    kinds: smallvec::smallvec![ArgKind::Any],
688                    required: true,
689                    by_ref: false,
690                    shape: ShapeKind::Scalar,
691                    coercion: CoercionPolicy::None,
692                    max: None,
693                    repeating: None,
694                    default: None,
695                },
696                // table_array (accepts both references and array literals)
697                ArgSchema {
698                    kinds: smallvec::smallvec![ArgKind::Any],
699                    required: true,
700                    by_ref: false,
701                    shape: ShapeKind::Range,
702                    coercion: CoercionPolicy::None,
703                    max: None,
704                    repeating: None,
705                    default: None,
706                },
707                // row_index_num (strict number)
708                ArgSchema {
709                    kinds: smallvec::smallvec![ArgKind::Number],
710                    required: true,
711                    by_ref: false,
712                    shape: ShapeKind::Scalar,
713                    coercion: CoercionPolicy::NumberStrict,
714                    max: None,
715                    repeating: None,
716                    default: None,
717                },
718                // range_lookup (optional logical, default FALSE for safer exact default)
719                ArgSchema {
720                    kinds: smallvec::smallvec![ArgKind::Logical],
721                    required: false,
722                    by_ref: false,
723                    shape: ShapeKind::Scalar,
724                    coercion: CoercionPolicy::Logical,
725                    max: None,
726                    repeating: None,
727                    default: Some(LiteralValue::Boolean(false)),
728                },
729            ]
730        });
731        &SCHEMA
732    }
733    fn eval<'a, 'b, 'c>(
734        &self,
735        args: &'c [ArgumentHandle<'a, 'b>],
736        ctx: &dyn FunctionContext<'b>,
737    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
738        if args.len() < 3 {
739            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
740                ExcelError::new(ExcelErrorKind::Na),
741            )));
742        }
743        let lookup_value = args[0].value()?.into_literal();
744
745        // Try to get table as reference, fall back to array literal
746        let table_ref_opt = args[1].as_reference_or_eval().ok();
747        let row_index = match args[2].value()?.into_literal() {
748            LiteralValue::Int(i) => i,
749            LiteralValue::Number(n) => n as i64,
750            _ => {
751                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
752                    ExcelError::new(ExcelErrorKind::Value),
753                )));
754            }
755        };
756        if row_index < 1 {
757            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
758                ExcelError::new(ExcelErrorKind::Value),
759            )));
760        }
761        let approximate = if args.len() >= 4 {
762            match args[3].value()?.into_literal() {
763                LiteralValue::Boolean(b) => b,
764                _ => true,
765            }
766        } else {
767            false
768        };
769        // Handle both cell references and array literals
770        if let Some(table_ref) = table_ref_opt {
771            let current_sheet = ctx.current_sheet();
772            let rv = ctx.resolve_range_view(&table_ref, current_sheet)?;
773            let (rows, cols) = rv.dims();
774            if row_index as usize > rows {
775                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
776                    ExcelError::new(ExcelErrorKind::Ref),
777                )));
778            }
779            let first_row_view = rv.sub_view(0, 0, 1, cols);
780            let col_idx_opt = if approximate {
781                let mut first_row: Vec<LiteralValue> = Vec::with_capacity(cols);
782                first_row_view.for_each_row(&mut |row| {
783                    if first_row.is_empty() {
784                        first_row.extend_from_slice(row);
785                    }
786                    Ok(())
787                })?;
788                binary_search_match(&first_row, &lookup_value, 1)
789            } else {
790                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
791                if !wildcard_mode
792                    && let Some(index) = ctx.get_lookup_index(&rv, LookupAxis::RowInView(0))
793                {
794                    index.find_first_exact(&lookup_value)
795                } else {
796                    super::lookup_utils::find_exact_index_in_view(
797                        &first_row_view,
798                        &lookup_value,
799                        wildcard_mode,
800                    )?
801                }
802            };
803
804            match col_idx_opt {
805                Some(i) => {
806                    let target_row_idx = (row_index - 1) as usize;
807                    let v = rv.get_cell(target_row_idx, i);
808                    let v = match v {
809                        LiteralValue::Empty => LiteralValue::Number(0.0),
810                        other => other,
811                    };
812                    Ok(crate::traits::CalcValue::Scalar(v))
813                }
814                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
815                    ExcelError::new(ExcelErrorKind::Na),
816                ))),
817            }
818        } else {
819            // Handle array literal
820            let v = args[1].value()?.into_literal();
821            let table: Vec<Vec<LiteralValue>> = match v {
822                LiteralValue::Array(rows) => rows,
823                other => vec![vec![other]],
824            };
825            if table.is_empty() {
826                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
827                    ExcelError::new(ExcelErrorKind::Na),
828                )));
829            }
830            let height = table.len();
831            if row_index as usize > height {
832                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
833                    ExcelError::new(ExcelErrorKind::Ref),
834                )));
835            }
836
837            // First row values for lookup
838            let first_row: Vec<LiteralValue> = table.first().cloned().unwrap_or_default();
839            let col_idx_opt = if approximate {
840                binary_search_match(&first_row, &lookup_value, 1)
841            } else {
842                let wildcard_mode = matches!(lookup_value, LiteralValue::Text(ref s) if s.contains('*') || s.contains('?') || s.contains('~'));
843                find_exact_index(&first_row, &lookup_value, wildcard_mode)
844            };
845
846            match col_idx_opt {
847                Some(i) => {
848                    let target_row_idx = (row_index - 1) as usize;
849                    let val = table
850                        .get(target_row_idx)
851                        .and_then(|r| r.get(i))
852                        .cloned()
853                        .unwrap_or(LiteralValue::Empty);
854                    let val = match val {
855                        LiteralValue::Empty => LiteralValue::Number(0.0),
856                        other => other,
857                    };
858                    Ok(crate::traits::CalcValue::Scalar(val))
859                }
860                None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
861                    ExcelError::new(ExcelErrorKind::Na),
862                ))),
863            }
864        }
865    }
866}
867
868pub fn register_builtins() {
869    use crate::function_registry::register_function;
870    use std::sync::Arc;
871    register_function(Arc::new(MatchFn));
872    register_function(Arc::new(VLookupFn));
873    register_function(Arc::new(HLookupFn));
874}
875
876#[cfg(test)]
877mod tests {
878    use super::*;
879    use crate::test_workbook::TestWorkbook;
880    use crate::traits::ArgumentHandle;
881    use formualizer_parse::parser::{ASTNode, ASTNodeType, ReferenceType};
882    use std::sync::Arc;
883    fn lit(v: LiteralValue) -> ASTNode {
884        ASTNode::new(ASTNodeType::Literal(v), None)
885    }
886
887    #[test]
888    fn match_wildcard_and_descending_and_unsorted() {
889        // Wildcard: A1:A4 = "foo", "fob", "bar", "baz"
890        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
891        let wb = wb
892            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("foo".into()))
893            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("fob".into()))
894            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("bar".into()))
895            .with_cell_a1("Sheet1", "A4", LiteralValue::Text("baz".into()));
896        let ctx = wb.interpreter();
897        let range = ASTNode::new(
898            ASTNodeType::Reference {
899                original: "A1:A4".into(),
900                reference: ReferenceType::range(None, Some(1), Some(1), Some(4), Some(1)),
901            },
902            None,
903        );
904        let f = ctx.context.get_function("", "MATCH").unwrap();
905        // Wildcard *o* matches "foo" (1) and "fob" (2), should return first match (1)
906        let pat = lit(LiteralValue::Text("*o*".into()));
907        let zero = lit(LiteralValue::Int(0));
908        let args = vec![
909            ArgumentHandle::new(&pat, &ctx),
910            ArgumentHandle::new(&range, &ctx),
911            ArgumentHandle::new(&zero, &ctx),
912        ];
913        let v = f
914            .dispatch(&args, &ctx.function_context(None))
915            .unwrap()
916            .into_literal();
917        assert_eq!(v, LiteralValue::Int(1));
918        // Wildcard b?z matches "baz" (4)
919        let pat2 = lit(LiteralValue::Text("b?z".into()));
920        let args2 = vec![
921            ArgumentHandle::new(&pat2, &ctx),
922            ArgumentHandle::new(&range, &ctx),
923            ArgumentHandle::new(&zero, &ctx),
924        ];
925        let v2 = f
926            .dispatch(&args2, &ctx.function_context(None))
927            .unwrap()
928            .into_literal();
929        assert_eq!(v2, LiteralValue::Int(4));
930        // No match
931        let pat3 = lit(LiteralValue::Text("z*".into()));
932        let args3 = vec![
933            ArgumentHandle::new(&pat3, &ctx),
934            ArgumentHandle::new(&range, &ctx),
935            ArgumentHandle::new(&zero, &ctx),
936        ];
937        let v3 = f
938            .dispatch(&args3, &ctx.function_context(None))
939            .unwrap()
940            .into_literal();
941        assert!(matches!(v3, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
942
943        // Descending approximate: 50,40,30,20,10; match_type = -1
944        let wb2 = TestWorkbook::new()
945            .with_function(Arc::new(MatchFn))
946            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(50))
947            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(40))
948            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
949            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(20))
950            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(10));
951        let ctx2 = wb2.interpreter();
952        let range2 = ASTNode::new(
953            ASTNodeType::Reference {
954                original: "A1:A5".into(),
955                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
956            },
957            None,
958        );
959        let minus1 = lit(LiteralValue::Int(-1));
960        let thirty = lit(LiteralValue::Int(30));
961        let args_desc = vec![
962            ArgumentHandle::new(&thirty, &ctx2),
963            ArgumentHandle::new(&range2, &ctx2),
964            ArgumentHandle::new(&minus1, &ctx2),
965        ];
966        let v_desc = f
967            .dispatch(&args_desc, &ctx2.function_context(None))
968            .unwrap()
969            .into_literal();
970        assert_eq!(v_desc, LiteralValue::Int(3));
971        // Descending, not found (needle > max)
972        let sixty = lit(LiteralValue::Int(60));
973        let args_desc2 = vec![
974            ArgumentHandle::new(&sixty, &ctx2),
975            ArgumentHandle::new(&range2, &ctx2),
976            ArgumentHandle::new(&minus1, &ctx2),
977        ];
978        let v_desc2 = f
979            .dispatch(&args_desc2, &ctx2.function_context(None))
980            .unwrap()
981            .into_literal();
982        assert!(matches!(v_desc2, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
983
984        // Unsorted detection: 10, 30, 20, 40, 50 (not sorted ascending)
985        let wb3 = TestWorkbook::new()
986            .with_function(Arc::new(MatchFn))
987            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
988            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
989            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20))
990            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(40))
991            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(50));
992        let ctx3 = wb3.interpreter();
993        let range3 = ASTNode::new(
994            ASTNodeType::Reference {
995                original: "A1:A5".into(),
996                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
997            },
998            None,
999        );
1000        let args_unsorted = vec![
1001            ArgumentHandle::new(&thirty, &ctx3),
1002            ArgumentHandle::new(&range3, &ctx3),
1003        ];
1004        let v_unsorted = f
1005            .dispatch(&args_unsorted, &ctx3.function_context(None))
1006            .unwrap()
1007            .into_literal();
1008        assert!(matches!(v_unsorted, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
1009        // Unsorted detection descending: 50, 30, 40, 20, 10
1010        let wb4 = TestWorkbook::new()
1011            .with_function(Arc::new(MatchFn))
1012            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(50))
1013            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(30))
1014            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(40))
1015            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(20))
1016            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(10));
1017        let ctx4 = wb4.interpreter();
1018        let range4 = ASTNode::new(
1019            ASTNodeType::Reference {
1020                original: "A1:A5".into(),
1021                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
1022            },
1023            None,
1024        );
1025        let args_unsorted_desc = vec![
1026            ArgumentHandle::new(&thirty, &ctx4),
1027            ArgumentHandle::new(&range4, &ctx4),
1028            ArgumentHandle::new(&minus1, &ctx4),
1029        ];
1030        let v_unsorted_desc = f
1031            .dispatch(&args_unsorted_desc, &ctx4.function_context(None))
1032            .unwrap()
1033            .into_literal();
1034        assert!(matches!(v_unsorted_desc, LiteralValue::Error(e) if e.kind == ExcelErrorKind::Na));
1035    }
1036
1037    #[test]
1038    fn match_unicode_exact_and_wildcard_are_case_insensitive() {
1039        let wb = TestWorkbook::new()
1040            .with_function(Arc::new(MatchFn))
1041            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("ИВАН".into()))
1042            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Петр".into()))
1043            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Иванов".into()));
1044        let ctx = wb.interpreter();
1045        let range = ASTNode::new(
1046            ASTNodeType::Reference {
1047                original: "A1:A3".into(),
1048                reference: ReferenceType::range(None, Some(1), Some(1), Some(3), Some(1)),
1049            },
1050            None,
1051        );
1052        let f = ctx.context.get_function("", "MATCH").unwrap();
1053        let zero = lit(LiteralValue::Int(0));
1054
1055        let exact = lit(LiteralValue::Text("иван".into()));
1056        let exact_args = vec![
1057            ArgumentHandle::new(&exact, &ctx),
1058            ArgumentHandle::new(&range, &ctx),
1059            ArgumentHandle::new(&zero, &ctx),
1060        ];
1061        let exact_v = f
1062            .dispatch(&exact_args, &ctx.function_context(None))
1063            .unwrap()
1064            .into_literal();
1065        assert_eq!(exact_v, LiteralValue::Int(1));
1066
1067        let pat = lit(LiteralValue::Text("ив?н*".into()));
1068        let pat_args = vec![
1069            ArgumentHandle::new(&pat, &ctx),
1070            ArgumentHandle::new(&range, &ctx),
1071            ArgumentHandle::new(&zero, &ctx),
1072        ];
1073        let pat_v = f
1074            .dispatch(&pat_args, &ctx.function_context(None))
1075            .unwrap()
1076            .into_literal();
1077        assert_eq!(pat_v, LiteralValue::Int(1));
1078    }
1079
1080    #[test]
1081    fn match_exact_and_approx() {
1082        let wb = TestWorkbook::new().with_function(Arc::new(MatchFn));
1083        let wb = wb
1084            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
1085            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
1086            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
1087            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(40))
1088            .with_cell_a1("Sheet1", "A5", LiteralValue::Int(50));
1089        let ctx = wb.interpreter();
1090        let range = ASTNode::new(
1091            ASTNodeType::Reference {
1092                original: "A1:A5".into(),
1093                reference: ReferenceType::range(None, Some(1), Some(1), Some(5), Some(1)),
1094            },
1095            None,
1096        );
1097        let f = ctx.context.get_function("", "MATCH").unwrap();
1098        let thirty = lit(LiteralValue::Int(30));
1099        let zero = lit(LiteralValue::Int(0));
1100        let args = vec![
1101            ArgumentHandle::new(&thirty, &ctx),
1102            ArgumentHandle::new(&range, &ctx),
1103            ArgumentHandle::new(&zero, &ctx),
1104        ];
1105        let v = f
1106            .dispatch(&args, &ctx.function_context(None))
1107            .unwrap()
1108            .into_literal();
1109        assert_eq!(v, LiteralValue::Int(3));
1110        let thirty_seven = lit(LiteralValue::Int(37));
1111        let args = vec![
1112            ArgumentHandle::new(&thirty_seven, &ctx),
1113            ArgumentHandle::new(&range, &ctx),
1114        ];
1115        let v = f
1116            .dispatch(&args, &ctx.function_context(None))
1117            .unwrap()
1118            .into_literal();
1119        assert_eq!(v, LiteralValue::Int(3));
1120    }
1121
1122    #[test]
1123    fn vlookup_basic() {
1124        let wb = TestWorkbook::new()
1125            .with_function(Arc::new(VLookupFn))
1126            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Key1".into()))
1127            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Key2".into()))
1128            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(100))
1129            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200));
1130        let ctx = wb.interpreter();
1131        let table = ASTNode::new(
1132            ASTNodeType::Reference {
1133                original: "A1:B2".into(),
1134                reference: ReferenceType::range(None, Some(1), Some(1), Some(2), Some(2)),
1135            },
1136            None,
1137        );
1138        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
1139        let key2 = lit(LiteralValue::Text("Key2".into()));
1140        let two = lit(LiteralValue::Int(2));
1141        let false_lit = lit(LiteralValue::Boolean(false));
1142        let args = vec![
1143            ArgumentHandle::new(&key2, &ctx),
1144            ArgumentHandle::new(&table, &ctx),
1145            ArgumentHandle::new(&two, &ctx),
1146            ArgumentHandle::new(&false_lit, &ctx),
1147        ];
1148        let v = f
1149            .dispatch(&args, &ctx.function_context(None))
1150            .unwrap()
1151            .into_literal();
1152        assert_eq!(v, LiteralValue::Number(200.0));
1153    }
1154
1155    #[test]
1156    fn vlookup_named_range_reference() {
1157        let wb = TestWorkbook::new()
1158            .with_function(Arc::new(VLookupFn))
1159            .with_named_range(
1160                "Split",
1161                vec![
1162                    vec![
1163                        LiteralValue::Text("Professional".into()),
1164                        LiteralValue::Int(123),
1165                    ],
1166                    vec![LiteralValue::Text("Support".into()), LiteralValue::Int(77)],
1167                ],
1168            );
1169        let ctx = wb.interpreter();
1170        let table = ASTNode::new(
1171            ASTNodeType::Reference {
1172                original: "Split".into(),
1173                reference: ReferenceType::NamedRange("Split".into()),
1174            },
1175            None,
1176        );
1177        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
1178        let key = lit(LiteralValue::Text("Professional".into()));
1179        let two = lit(LiteralValue::Int(2));
1180        let false_lit = lit(LiteralValue::Boolean(false));
1181        let args = vec![
1182            ArgumentHandle::new(&key, &ctx),
1183            ArgumentHandle::new(&table, &ctx),
1184            ArgumentHandle::new(&two, &ctx),
1185            ArgumentHandle::new(&false_lit, &ctx),
1186        ];
1187        let v = f
1188            .dispatch(&args, &ctx.function_context(None))
1189            .unwrap()
1190            .into_literal();
1191        assert_eq!(v, LiteralValue::Number(123.0));
1192    }
1193
1194    #[test]
1195    fn vlookup_blank_target_cell_returns_zero() {
1196        // Excel treats a direct reference to an empty cell as 0.
1197        // VLOOKUP should therefore return 0 (not Empty) when the found cell is empty.
1198        let wb = TestWorkbook::new()
1199            .with_function(Arc::new(VLookupFn))
1200            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1));
1201
1202        let ctx = wb.interpreter();
1203        let table = ASTNode::new(
1204            ASTNodeType::Reference {
1205                original: "A1:B1".into(),
1206                reference: ReferenceType::range(None, Some(1), Some(1), Some(1), Some(2)),
1207            },
1208            None,
1209        );
1210        let f = ctx.context.get_function("", "VLOOKUP").unwrap();
1211        let key1 = lit(LiteralValue::Int(1));
1212        let two = lit(LiteralValue::Int(2));
1213        let false_lit = lit(LiteralValue::Boolean(false));
1214        let args = vec![
1215            ArgumentHandle::new(&key1, &ctx),
1216            ArgumentHandle::new(&table, &ctx),
1217            ArgumentHandle::new(&two, &ctx),
1218            ArgumentHandle::new(&false_lit, &ctx),
1219        ];
1220        let v = f
1221            .dispatch(&args, &ctx.function_context(None))
1222            .unwrap()
1223            .into_literal();
1224        assert_eq!(v, LiteralValue::Number(0.0));
1225    }
1226
1227    #[test]
1228    fn hlookup_basic() {
1229        let wb = TestWorkbook::new()
1230            .with_function(Arc::new(HLookupFn))
1231            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Key1".into()))
1232            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("Key2".into()))
1233            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(100))
1234            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200));
1235        let ctx = wb.interpreter();
1236        let table = ASTNode::new(
1237            ASTNodeType::Reference {
1238                original: "A1:B2".into(),
1239                reference: ReferenceType::range(None, Some(1), Some(1), Some(2), Some(2)),
1240            },
1241            None,
1242        );
1243        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
1244        let key1 = lit(LiteralValue::Text("Key1".into()));
1245        let two = lit(LiteralValue::Int(2));
1246        let false_lit = lit(LiteralValue::Boolean(false));
1247        let args = vec![
1248            ArgumentHandle::new(&key1, &ctx),
1249            ArgumentHandle::new(&table, &ctx),
1250            ArgumentHandle::new(&two, &ctx),
1251            ArgumentHandle::new(&false_lit, &ctx),
1252        ];
1253        let v = f
1254            .dispatch(&args, &ctx.function_context(None))
1255            .unwrap()
1256            .into_literal();
1257        assert_eq!(v, LiteralValue::Number(100.0));
1258    }
1259
1260    #[test]
1261    fn hlookup_blank_target_cell_returns_zero() {
1262        let wb = TestWorkbook::new()
1263            .with_function(Arc::new(HLookupFn))
1264            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1));
1265
1266        let ctx = wb.interpreter();
1267        let table = ASTNode::new(
1268            ASTNodeType::Reference {
1269                original: "A1:B2".into(),
1270                reference: ReferenceType::range(None, Some(1), Some(1), Some(2), Some(2)),
1271            },
1272            None,
1273        );
1274        let f = ctx.context.get_function("", "HLOOKUP").unwrap();
1275        let key1 = lit(LiteralValue::Int(1));
1276        let two = lit(LiteralValue::Int(2));
1277        let false_lit = lit(LiteralValue::Boolean(false));
1278        let args = vec![
1279            ArgumentHandle::new(&key1, &ctx),
1280            ArgumentHandle::new(&table, &ctx),
1281            ArgumentHandle::new(&two, &ctx),
1282            ArgumentHandle::new(&false_lit, &ctx),
1283        ];
1284        let v = f
1285            .dispatch(&args, &ctx.function_context(None))
1286            .unwrap()
1287            .into_literal();
1288        assert_eq!(v, LiteralValue::Number(0.0));
1289    }
1290}
formualizer_eval/builtins/lookup/core.rs

formualizer_eval/builtins/lookup/
core.rs
