From f280c03345dc2a207ac319182da182a0f0fbb963 Mon Sep 17 00:00:00 2001
From: Stani <spe.stani.be@gmail.com>
Date: Sat, 21 Aug 2021 05:58:15 +0200
Subject: This closes #997, fix LOOKUP function to find nearest match (#1001)

---
 calc.go      | 15 +++++++++++++--
 calc_test.go |  2 ++
 2 files changed, 15 insertions(+), 2 deletions(-)

diff --git a/calc.go b/calc.go
index a434709..9ff0535 100644
--- a/calc.go
+++ b/calc.go
@@ -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
-- 
cgit v1.2.1