Skip to main content

sheetkit_core/formula/functions/
lookup.rs

1//! Lookup and reference formula functions: VLOOKUP, HLOOKUP, INDEX, MATCH,
2//! LOOKUP, ROW, COLUMN, ROWS, COLUMNS, CHOOSE, ADDRESS.
3
4use crate::cell::CellValue;
5use crate::error::{Error, Result};
6use crate::formula::ast::{CellReference, Expr};
7use crate::formula::eval::{coerce_to_number, coerce_to_string, compare_values, Evaluator};
8use crate::formula::functions::check_arg_count;
9use crate::formula::functions::conversion::{
10    checked_trunc_count, checked_trunc_i32, checked_trunc_i64,
11};
12use crate::utils::cell_ref::{column_name_to_number, column_number_to_name};
13
14/// Extract (start, end) CellReference pair from a Range expression.
15fn extract_range(expr: &Expr) -> Result<(&CellReference, &CellReference)> {
16    match expr {
17        Expr::Range { start, end } => Ok((start, end)),
18        _ => Err(Error::FormulaError(
19            "expected a range reference".to_string(),
20        )),
21    }
22}
23
24/// Read a range into a flat row-major Vec and return (values, num_cols, num_rows).
25fn read_range(expr: &Expr, ctx: &mut Evaluator) -> Result<(Vec<CellValue>, usize, usize)> {
26    let (start, end) = extract_range(expr)?;
27    let start_col = column_name_to_number(&start.col)?;
28    let end_col = column_name_to_number(&end.col)?;
29    let min_col = start_col.min(end_col);
30    let max_col = start_col.max(end_col);
31    let min_row = start.row.min(end.row);
32    let max_row = start.row.max(end.row);
33    let num_cols = (max_col - min_col + 1) as usize;
34    let num_rows = (max_row - min_row + 1) as usize;
35    let values = ctx.expand_range(start, end)?;
36    Ok((values, num_cols, num_rows))
37}
38
39/// VLOOKUP(lookup_value, table_array, col_index_num, \[range_lookup\])
40pub fn fn_vlookup(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
41    check_arg_count("VLOOKUP", args, 3, 4)?;
42    let lookup_val = ctx.eval_expr(&args[0])?;
43    let (values, num_cols, num_rows) = read_range(&args[1], ctx)?;
44    let col_index = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[2])?)?).unwrap_or(0);
45    let range_lookup = if args.len() > 3 {
46        match ctx.eval_expr(&args[3])? {
47            CellValue::Bool(b) => b,
48            v => coerce_to_number(&v)? != 0.0,
49        }
50    } else {
51        true
52    };
53
54    if col_index < 1 || col_index > num_cols {
55        return Ok(CellValue::Error("#REF!".to_string()));
56    }
57
58    if range_lookup {
59        // Approximate match: find largest value <= lookup_val in first column
60        let mut best_row: Option<usize> = None;
61        for r in 0..num_rows {
62            let cell = &values[r * num_cols];
63            if compare_values(cell, &lookup_val) != std::cmp::Ordering::Greater {
64                best_row = Some(r);
65            } else {
66                break;
67            }
68        }
69        match best_row {
70            Some(r) => Ok(values[r * num_cols + col_index - 1].clone()),
71            None => Ok(CellValue::Error("#N/A".to_string())),
72        }
73    } else {
74        // Exact match
75        for r in 0..num_rows {
76            let cell = &values[r * num_cols];
77            if compare_values(cell, &lookup_val) == std::cmp::Ordering::Equal {
78                return Ok(values[r * num_cols + col_index - 1].clone());
79            }
80        }
81        Ok(CellValue::Error("#N/A".to_string()))
82    }
83}
84
85/// HLOOKUP(lookup_value, table_array, row_index_num, \[range_lookup\])
86pub fn fn_hlookup(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
87    check_arg_count("HLOOKUP", args, 3, 4)?;
88    let lookup_val = ctx.eval_expr(&args[0])?;
89    let (values, num_cols, num_rows) = read_range(&args[1], ctx)?;
90    let row_index = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[2])?)?).unwrap_or(0);
91    let range_lookup = if args.len() > 3 {
92        match ctx.eval_expr(&args[3])? {
93            CellValue::Bool(b) => b,
94            v => coerce_to_number(&v)? != 0.0,
95        }
96    } else {
97        true
98    };
99
100    if row_index < 1 || row_index > num_rows {
101        return Ok(CellValue::Error("#REF!".to_string()));
102    }
103
104    if range_lookup {
105        let mut best_col: Option<usize> = None;
106        for (c, cell) in values.iter().enumerate().take(num_cols) {
107            if compare_values(cell, &lookup_val) != std::cmp::Ordering::Greater {
108                best_col = Some(c);
109            } else {
110                break;
111            }
112        }
113        match best_col {
114            Some(c) => Ok(values[(row_index - 1) * num_cols + c].clone()),
115            None => Ok(CellValue::Error("#N/A".to_string())),
116        }
117    } else {
118        for (c, cell) in values.iter().enumerate().take(num_cols) {
119            if compare_values(cell, &lookup_val) == std::cmp::Ordering::Equal {
120                return Ok(values[(row_index - 1) * num_cols + c].clone());
121            }
122        }
123        Ok(CellValue::Error("#N/A".to_string()))
124    }
125}
126
127/// INDEX(array, row_num, \[col_num\])
128pub fn fn_index(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
129    check_arg_count("INDEX", args, 2, 3)?;
130    let (values, num_cols, num_rows) = read_range(&args[0], ctx)?;
131    let row_num = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[1])?)?).unwrap_or(0);
132    let col_num = if args.len() > 2 {
133        checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[2])?)?).unwrap_or(0)
134    } else {
135        1
136    };
137
138    if row_num < 1 || row_num > num_rows || col_num < 1 || col_num > num_cols {
139        return Ok(CellValue::Error("#REF!".to_string()));
140    }
141    Ok(values[(row_num - 1) * num_cols + (col_num - 1)].clone())
142}
143
144/// MATCH(lookup_value, lookup_array, \[match_type\])
145pub fn fn_match(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
146    check_arg_count("MATCH", args, 2, 3)?;
147    let lookup_val = ctx.eval_expr(&args[0])?;
148    let (values, num_cols, num_rows) = read_range(&args[1], ctx)?;
149    let match_type = if args.len() > 2 {
150        let value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
151        let Some(match_type) = checked_trunc_i32(value) else {
152            return Ok(CellValue::Error("#N/A".to_string()));
153        };
154        match_type
155    } else {
156        1
157    };
158
159    // MATCH works on a 1-D array (single row or column)
160    let items: &[CellValue] = if num_rows == 1 || num_cols == 1 {
161        &values
162    } else {
163        return Ok(CellValue::Error("#N/A".to_string()));
164    };
165
166    match match_type {
167        0 => {
168            // Exact match
169            for (i, v) in items.iter().enumerate() {
170                if compare_values(v, &lookup_val) == std::cmp::Ordering::Equal {
171                    return Ok(CellValue::Number((i + 1) as f64));
172                }
173            }
174            Ok(CellValue::Error("#N/A".to_string()))
175        }
176        1 => {
177            // Largest value <= lookup_val (data sorted ascending)
178            let mut best: Option<usize> = None;
179            for (i, v) in items.iter().enumerate() {
180                if compare_values(v, &lookup_val) != std::cmp::Ordering::Greater {
181                    best = Some(i);
182                }
183            }
184            match best {
185                Some(i) => Ok(CellValue::Number((i + 1) as f64)),
186                None => Ok(CellValue::Error("#N/A".to_string())),
187            }
188        }
189        -1 => {
190            // Smallest value >= lookup_val (data sorted descending)
191            let mut best: Option<usize> = None;
192            for (i, v) in items.iter().enumerate() {
193                if compare_values(v, &lookup_val) != std::cmp::Ordering::Less {
194                    best = Some(i);
195                }
196            }
197            match best {
198                Some(i) => Ok(CellValue::Number((i + 1) as f64)),
199                None => Ok(CellValue::Error("#N/A".to_string())),
200            }
201        }
202        _ => Ok(CellValue::Error("#N/A".to_string())),
203    }
204}
205
206/// LOOKUP(lookup_value, lookup_vector, \[result_vector\]) - vector form.
207pub fn fn_lookup(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
208    check_arg_count("LOOKUP", args, 2, 3)?;
209    let lookup_val = ctx.eval_expr(&args[0])?;
210    let (lookup_values, _, _) = read_range(&args[1], ctx)?;
211
212    // Find largest value <= lookup_val (assumes sorted ascending)
213    let mut best: Option<usize> = None;
214    for (i, v) in lookup_values.iter().enumerate() {
215        if compare_values(v, &lookup_val) != std::cmp::Ordering::Greater {
216            best = Some(i);
217        }
218    }
219    let idx = match best {
220        Some(i) => i,
221        None => return Ok(CellValue::Error("#N/A".to_string())),
222    };
223
224    if args.len() > 2 {
225        let (result_values, _, _) = read_range(&args[2], ctx)?;
226        if idx < result_values.len() {
227            Ok(result_values[idx].clone())
228        } else {
229            Ok(CellValue::Error("#N/A".to_string()))
230        }
231    } else {
232        Ok(lookup_values[idx].clone())
233    }
234}
235
236/// ROW([reference]) - returns the row number of a reference.
237pub fn fn_row(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
238    check_arg_count("ROW", args, 0, 1)?;
239    if args.is_empty() {
240        return Ok(CellValue::Number(ctx.position().1 as f64));
241    }
242    match &args[0] {
243        Expr::CellRef(cell_ref) => Ok(CellValue::Number(cell_ref.row as f64)),
244        Expr::Range { start, .. } => Ok(CellValue::Number(start.row as f64)),
245        _ => Ok(CellValue::Error("#VALUE!".to_string())),
246    }
247}
248
249/// COLUMN([reference]) - returns the column number of a reference.
250pub fn fn_column(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
251    check_arg_count("COLUMN", args, 0, 1)?;
252    if args.is_empty() {
253        return Ok(CellValue::Number(ctx.position().0 as f64));
254    }
255    match &args[0] {
256        Expr::CellRef(cell_ref) => {
257            let col = column_name_to_number(&cell_ref.col)?;
258            Ok(CellValue::Number(col as f64))
259        }
260        Expr::Range { start, .. } => {
261            let col = column_name_to_number(&start.col)?;
262            Ok(CellValue::Number(col as f64))
263        }
264        _ => Ok(CellValue::Error("#VALUE!".to_string())),
265    }
266}
267
268/// ROWS(array) - returns the number of rows in a reference.
269pub fn fn_rows(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
270    check_arg_count("ROWS", args, 1, 1)?;
271    match &args[0] {
272        Expr::Range { start, end } => {
273            let rows = (end.row.max(start.row) - end.row.min(start.row) + 1) as f64;
274            Ok(CellValue::Number(rows))
275        }
276        Expr::CellRef(_) => Ok(CellValue::Number(1.0)),
277        _ => Ok(CellValue::Error("#VALUE!".to_string())),
278    }
279}
280
281/// COLUMNS(array) - returns the number of columns in a reference.
282pub fn fn_columns(args: &[Expr], _ctx: &mut Evaluator) -> Result<CellValue> {
283    check_arg_count("COLUMNS", args, 1, 1)?;
284    match &args[0] {
285        Expr::Range { start, end } => {
286            let start_col = column_name_to_number(&start.col)?;
287            let end_col = column_name_to_number(&end.col)?;
288            let cols = (start_col.max(end_col) - start_col.min(end_col) + 1) as f64;
289            Ok(CellValue::Number(cols))
290        }
291        Expr::CellRef(_) => Ok(CellValue::Number(1.0)),
292        _ => Ok(CellValue::Error("#VALUE!".to_string())),
293    }
294}
295
296/// CHOOSE(index_num, value1, \[value2\], ...) - returns value at given index.
297pub fn fn_choose(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
298    check_arg_count("CHOOSE", args, 2, 255)?;
299    let index = checked_trunc_count(coerce_to_number(&ctx.eval_expr(&args[0])?)?).unwrap_or(0);
300    if index < 1 || index >= args.len() {
301        return Ok(CellValue::Error("#VALUE!".to_string()));
302    }
303    ctx.eval_expr(&args[index])
304}
305
306/// ADDRESS(row_num, column_num, \[abs_num\], \[a1\], \[sheet_text\])
307pub fn fn_address(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
308    check_arg_count("ADDRESS", args, 2, 5)?;
309    let row = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
310    let col = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
311    let (Some(row), Some(col)) = (
312        checked_trunc_i64(row, 0, i64::from(u32::MAX)),
313        checked_trunc_i64(col, 0, i64::from(u32::MAX)),
314    ) else {
315        return Ok(CellValue::Error("#VALUE!".to_string()));
316    };
317    let (Ok(row), Ok(col)) = (u32::try_from(row), u32::try_from(col)) else {
318        return Ok(CellValue::Error("#VALUE!".to_string()));
319    };
320    let abs_num = if args.len() > 2 {
321        let value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
322        let Some(abs_num) = checked_trunc_i32(value) else {
323            return Ok(CellValue::Error("#VALUE!".to_string()));
324        };
325        abs_num
326    } else {
327        1
328    };
329    // a1 parameter (default true = A1 style, false = R1C1 style)
330    let a1 = if args.len() > 3 {
331        match ctx.eval_expr(&args[3])? {
332            CellValue::Bool(b) => b,
333            v => coerce_to_number(&v)? != 0.0,
334        }
335    } else {
336        true
337    };
338    let sheet_text = if args.len() > 4 {
339        Some(coerce_to_string(&ctx.eval_expr(&args[4])?))
340    } else {
341        None
342    };
343
344    if col < 1 || row < 1 {
345        return Ok(CellValue::Error("#VALUE!".to_string()));
346    }
347
348    let col_name = column_number_to_name(col)?;
349
350    let address = if a1 {
351        match abs_num {
352            1 => format!("${col_name}${row}"), // absolute row and column
353            2 => format!("{col_name}${row}"),  // absolute row
354            3 => format!("${col_name}{row}"),  // absolute column
355            4 => format!("{col_name}{row}"),   // relative
356            _ => return Ok(CellValue::Error("#VALUE!".to_string())),
357        }
358    } else {
359        // R1C1 style
360        match abs_num {
361            1 => format!("R{row}C{col}"),
362            2 => format!("R{row}C[{col}]"),
363            3 => format!("R[{row}]C{col}"),
364            4 => format!("R[{row}]C[{col}]"),
365            _ => return Ok(CellValue::Error("#VALUE!".to_string())),
366        }
367    };
368
369    if let Some(sheet) = sheet_text {
370        Ok(CellValue::String(format!("{sheet}!{address}")))
371    } else {
372        Ok(CellValue::String(address))
373    }
374}
375
376#[cfg(test)]
377mod tests {
378    use super::*;
379    use crate::formula::eval::{evaluate, CellSnapshot};
380    use crate::formula::parser::parse_formula;
381
382    fn eval(formula: &str) -> CellValue {
383        let snap = CellSnapshot::new("Sheet1".to_string());
384        let expr = parse_formula(formula).unwrap();
385        evaluate(&expr, &snap).unwrap()
386    }
387
388    fn eval_with_data(formula: &str, snap: &CellSnapshot) -> CellValue {
389        let expr = parse_formula(formula).unwrap();
390        evaluate(&expr, snap).unwrap()
391    }
392
393    #[test]
394    fn test_vlookup_exact() {
395        let mut snap = CellSnapshot::new("Sheet1".to_string());
396        // Column A: keys, Column B: values
397        snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
398        snap.set_cell("Sheet1", 2, 1, CellValue::String("one".to_string()));
399        snap.set_cell("Sheet1", 1, 2, CellValue::Number(2.0));
400        snap.set_cell("Sheet1", 2, 2, CellValue::String("two".to_string()));
401        snap.set_cell("Sheet1", 1, 3, CellValue::Number(3.0));
402        snap.set_cell("Sheet1", 2, 3, CellValue::String("three".to_string()));
403
404        assert_eq!(
405            eval_with_data("VLOOKUP(2,A1:B3,2,FALSE)", &snap),
406            CellValue::String("two".to_string())
407        );
408    }
409
410    #[test]
411    fn test_vlookup_not_found() {
412        let mut snap = CellSnapshot::new("Sheet1".to_string());
413        snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
414        snap.set_cell("Sheet1", 2, 1, CellValue::String("one".to_string()));
415
416        assert_eq!(
417            eval_with_data("VLOOKUP(99,A1:B1,2,FALSE)", &snap),
418            CellValue::Error("#N/A".to_string())
419        );
420    }
421
422    #[test]
423    fn test_vlookup_approximate() {
424        let mut snap = CellSnapshot::new("Sheet1".to_string());
425        snap.set_cell("Sheet1", 1, 1, CellValue::Number(10.0));
426        snap.set_cell("Sheet1", 2, 1, CellValue::String("ten".to_string()));
427        snap.set_cell("Sheet1", 1, 2, CellValue::Number(20.0));
428        snap.set_cell("Sheet1", 2, 2, CellValue::String("twenty".to_string()));
429        snap.set_cell("Sheet1", 1, 3, CellValue::Number(30.0));
430        snap.set_cell("Sheet1", 2, 3, CellValue::String("thirty".to_string()));
431
432        assert_eq!(
433            eval_with_data("VLOOKUP(25,A1:B3,2,TRUE)", &snap),
434            CellValue::String("twenty".to_string())
435        );
436    }
437
438    #[test]
439    fn test_hlookup_exact() {
440        let mut snap = CellSnapshot::new("Sheet1".to_string());
441        // Row 1: keys, Row 2: values
442        snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
443        snap.set_cell("Sheet1", 2, 1, CellValue::Number(2.0));
444        snap.set_cell("Sheet1", 3, 1, CellValue::Number(3.0));
445        snap.set_cell("Sheet1", 1, 2, CellValue::String("one".to_string()));
446        snap.set_cell("Sheet1", 2, 2, CellValue::String("two".to_string()));
447        snap.set_cell("Sheet1", 3, 2, CellValue::String("three".to_string()));
448
449        assert_eq!(
450            eval_with_data("HLOOKUP(2,A1:C2,2,FALSE)", &snap),
451            CellValue::String("two".to_string())
452        );
453    }
454
455    #[test]
456    fn test_index_basic() {
457        let mut snap = CellSnapshot::new("Sheet1".to_string());
458        snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
459        snap.set_cell("Sheet1", 2, 1, CellValue::Number(2.0));
460        snap.set_cell("Sheet1", 1, 2, CellValue::Number(3.0));
461        snap.set_cell("Sheet1", 2, 2, CellValue::Number(4.0));
462
463        assert_eq!(
464            eval_with_data("INDEX(A1:B2,2,2)", &snap),
465            CellValue::Number(4.0)
466        );
467    }
468
469    #[test]
470    fn test_match_exact() {
471        let mut snap = CellSnapshot::new("Sheet1".to_string());
472        snap.set_cell("Sheet1", 1, 1, CellValue::String("apple".to_string()));
473        snap.set_cell("Sheet1", 1, 2, CellValue::String("banana".to_string()));
474        snap.set_cell("Sheet1", 1, 3, CellValue::String("cherry".to_string()));
475
476        assert_eq!(
477            eval_with_data(r#"MATCH("banana",A1:A3,0)"#, &snap),
478            CellValue::Number(2.0)
479        );
480    }
481
482    #[test]
483    fn test_match_not_found() {
484        let mut snap = CellSnapshot::new("Sheet1".to_string());
485        snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
486        snap.set_cell("Sheet1", 1, 2, CellValue::Number(2.0));
487
488        assert_eq!(
489            eval_with_data("MATCH(99,A1:A2,0)", &snap),
490            CellValue::Error("#N/A".to_string())
491        );
492    }
493
494    #[test]
495    fn test_lookup_vector() {
496        let mut snap = CellSnapshot::new("Sheet1".to_string());
497        snap.set_cell("Sheet1", 1, 1, CellValue::Number(1.0));
498        snap.set_cell("Sheet1", 1, 2, CellValue::Number(2.0));
499        snap.set_cell("Sheet1", 1, 3, CellValue::Number(3.0));
500        snap.set_cell("Sheet1", 2, 1, CellValue::String("one".to_string()));
501        snap.set_cell("Sheet1", 2, 2, CellValue::String("two".to_string()));
502        snap.set_cell("Sheet1", 2, 3, CellValue::String("three".to_string()));
503
504        assert_eq!(
505            eval_with_data("LOOKUP(2,A1:A3,B1:B3)", &snap),
506            CellValue::String("two".to_string())
507        );
508    }
509
510    #[test]
511    fn test_row() {
512        assert_eq!(eval("ROW(B5)"), CellValue::Number(5.0));
513    }
514
515    #[test]
516    fn test_column() {
517        assert_eq!(eval("COLUMN(C1)"), CellValue::Number(3.0));
518    }
519
520    #[test]
521    fn test_rows() {
522        assert_eq!(eval("ROWS(A1:A10)"), CellValue::Number(10.0));
523    }
524
525    #[test]
526    fn test_columns() {
527        assert_eq!(eval("COLUMNS(A1:D1)"), CellValue::Number(4.0));
528    }
529
530    #[test]
531    fn test_choose() {
532        assert_eq!(
533            eval(r#"CHOOSE(2,"a","b","c")"#),
534            CellValue::String("b".to_string())
535        );
536    }
537
538    #[test]
539    fn test_choose_out_of_range() {
540        assert_eq!(
541            eval(r#"CHOOSE(5,"a","b","c")"#),
542            CellValue::Error("#VALUE!".to_string())
543        );
544    }
545
546    #[test]
547    fn test_address_absolute() {
548        assert_eq!(eval("ADDRESS(1,1)"), CellValue::String("$A$1".to_string()));
549    }
550
551    #[test]
552    fn test_address_relative() {
553        assert_eq!(eval("ADDRESS(1,1,4)"), CellValue::String("A1".to_string()));
554    }
555
556    #[test]
557    fn test_address_with_sheet() {
558        assert_eq!(
559            eval(r#"ADDRESS(1,1,1,TRUE,"Sheet2")"#),
560            CellValue::String("Sheet2!$A$1".to_string())
561        );
562    }
563
564    #[test]
565    fn test_row_no_args() {
566        assert_eq!(eval("ROW()"), CellValue::Number(1.0));
567    }
568
569    #[test]
570    fn test_column_no_args() {
571        assert_eq!(eval("COLUMN()"), CellValue::Number(1.0));
572    }
573}