diff options
| -rw-r--r-- | calc.go | 15 | ||||
| -rw-r--r-- | calc_test.go | 2 |
2 files changed, 15 insertions, 2 deletions
@@ -7572,7 +7572,7 @@ func (fn *formulaFuncs) LOOKUP(argsList *list.List) formulaArg { if arrayForm && len(lookupVector.Matrix) == 0 { return newErrorFormulaArg(formulaErrorVALUE, "LOOKUP requires not empty range as second argument") } - cols, matchIdx := lookupCol(lookupVector, 0), -1 + cols, matchIdx, ok := lookupCol(lookupVector, 0), -1, false for idx, col := range cols { lhs := lookupValue switch col.Type { @@ -7584,10 +7584,21 @@ func (fn *formulaFuncs) LOOKUP(argsList *list.List) formulaArg { } } } - if compareFormulaArg(lhs, col, false, false) == criteriaEq { + compare := compareFormulaArg(lhs, col, false, false) + // Find exact match + if compare == criteriaEq { matchIdx = idx break } + // Find nearest match if lookup value is more than or equal to the first value in lookup vector + if idx == 0 { + ok = compare == criteriaG + } else if ok && compare == criteriaL && matchIdx == -1 { + matchIdx = idx - 1 + } + } + if ok && matchIdx == -1 { + matchIdx = len(cols) - 1 } var column []formulaArg if argsList.Len() == 3 { diff --git a/calc_test.go b/calc_test.go index 7c107f3..8deab93 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1151,6 +1151,8 @@ func TestCalcCellValue(t *testing.T) { "=LOOKUP(F8,F8:F9,D8:D9)": "Feb", "=LOOKUP(E3,E2:E5,F2:F5)": "22100", "=LOOKUP(E3,E2:F5)": "22100", + "=LOOKUP(F3+1,F3:F4,F3:F4)": "22100", + "=LOOKUP(F4+1,F3:F4,F3:F4)": "53321", "=LOOKUP(1,MUNIT(1))": "1", "=LOOKUP(1,MUNIT(1),MUNIT(1))": "1", // ROW |