diff options
| author | Stani <spe.stani.be@gmail.com> | 2021-08-21 05:58:15 +0200 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2021-08-21 11:58:15 +0800 |
| commit | f280c03345dc2a207ac319182da182a0f0fbb963 (patch) | |
| tree | 4539abcb3d681762ae34726942b78dee9d144856 | |
| parent | 935af2e356ff60c88761db1fc9a6be8f8c67a4f5 (diff) | |
This closes #997, fix LOOKUP function to find nearest match (#1001)
| -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 |