binary search in range lookup and new formula function: LOOKUP

3 files changed, 315 insertions, 89 deletions

diff --git a/LICENSE b/LICENSE
index e0f34bb..17591f2 100644
--- a/LICENSE
+++ b/LICENSE
@@ -1,6 +1,6 @@
 BSD 3-Clause License
 
-Copyright (c) 2016-2020 The excelize Authors.
+Copyright (c) 2016-2021 The excelize Authors.
 All rights reserved.
 
 Redistribution and use in source and binary forms, with or without
diff --git a/calc.go b/calc.go
index df2230e..450a788 100644
--- a/calc.go
+++ b/calc.go
@@ -223,6 +223,7 @@ var tokenPriority = map[string]int{
 //    FLOOR.MATH
 //    FLOOR.PRECISE
 //    GCD
+//    HLOOKUP
 //    IF
 //    INT
 //    ISBLANK
@@ -239,6 +240,7 @@ var tokenPriority = map[string]int{
 //    LN
 //    LOG
 //    LOG10
+//    LOOKUP
 //    LOWER
 //    MDETERM
 //    MEDIAN
@@ -275,6 +277,7 @@ var tokenPriority = map[string]int{
 //    TRIM
 //    TRUNC
 //    UPPER
+//    VLOOKUP
 //
 func (f *File) CalcCellValue(sheet, cell string) (result string, err error) {
 	var (
@@ -2335,8 +2338,8 @@ func (fn *formulaFuncs) MUNIT(argsList *list.List) (result formulaArg) {
 		return newErrorFormulaArg(formulaErrorVALUE, "MUNIT requires 1 numeric argument")
 	}
 	dimension := argsList.Back().Value.(formulaArg).ToNumber()
-	if dimension.Type == ArgError {
-		return dimension
+	if dimension.Type == ArgError || dimension.Number < 0 {
+		return newErrorFormulaArg(formulaErrorVALUE, dimension.Error)
 	}
 	matrix := make([][]formulaArg, 0, int(dimension.Number))
 	for i := 0; i < int(dimension.Number); i++ {
@@ -3607,8 +3610,7 @@ func matchPattern(pattern, name string) (matched bool) {
 	if pattern == "*" {
 		return true
 	}
-	rname := make([]rune, 0, len(name))
-	rpattern := make([]rune, 0, len(pattern))
+	rname, rpattern := make([]rune, 0, len(name)), make([]rune, 0, len(pattern))
 	for _, r := range name {
 		rname = append(rname, r)
 	}
@@ -3636,11 +3638,9 @@ func compareFormulaArg(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte
 		}
 		return criteriaG
 	case ArgString:
-		ls := lhs.String
-		rs := rhs.String
+		ls, rs := lhs.String, rhs.String
 		if !caseSensitive {
-			ls = strings.ToLower(ls)
-			rs = strings.ToLower(rs)
+			ls, rs = strings.ToLower(ls), strings.ToLower(rs)
 		}
 		if exactMatch {
 			match := matchPattern(rs, ls)
@@ -3649,7 +3649,15 @@ func compareFormulaArg(lhs, rhs formulaArg, caseSensitive, exactMatch bool) byte
 			}
 			return criteriaG
 		}
-		return byte(strings.Compare(ls, rs))
+		switch strings.Compare(ls, rs) {
+		case 1:
+			return criteriaG
+		case -1:
+			return criteriaL
+		case 0:
+			return criteriaEq
+		}
+		return criteriaErr
 	case ArgEmpty:
 		return criteriaEq
 	case ArgList:
@@ -3739,16 +3747,29 @@ func (fn *formulaFuncs) HLOOKUP(argsList *list.List) formulaArg {
 		}
 	}
 	row := tableArray.Matrix[0]
-start:
-	for idx, mtx := range row {
-		switch compareFormulaArg(mtx, lookupValue, false, exactMatch) {
-		case criteriaL:
-			matchIdx = idx
-		case criteriaEq:
-			matchIdx = idx
-			wasExact = true
-			break start
+	if exactMatch || len(tableArray.Matrix) == TotalRows {
+	start:
+		for idx, mtx := range row {
+			lhs := mtx
+			switch lookupValue.Type {
+			case ArgNumber:
+				if !lookupValue.Boolean {
+					lhs = mtx.ToNumber()
+					if lhs.Type == ArgError {
+						lhs = mtx
+					}
+				}
+			case ArgMatrix:
+				lhs = tableArray
+			}
+			if compareFormulaArg(lhs, lookupValue, false, exactMatch) == criteriaEq {
+				matchIdx = idx
+				wasExact = true
+				break start
+			}
 		}
+	} else {
+		matchIdx, wasExact = hlookupBinarySearch(row, lookupValue)
 	}
 	if matchIdx == -1 {
 		return newErrorFormulaArg(formulaErrorNA, "HLOOKUP no result found")
@@ -3795,11 +3816,51 @@ func (fn *formulaFuncs) VLOOKUP(argsList *list.List) formulaArg {
 			exactMatch = true
 		}
 	}
-start:
-	for idx, mtx := range tableArray.Matrix {
-		if len(mtx) == 0 {
-			continue
+	if exactMatch || len(tableArray.Matrix) == TotalRows {
+	start:
+		for idx, mtx := range tableArray.Matrix {
+			lhs := mtx[0]
+			switch lookupValue.Type {
+			case ArgNumber:
+				if !lookupValue.Boolean {
+					lhs = mtx[0].ToNumber()
+					if lhs.Type == ArgError {
+						lhs = mtx[0]
+					}
+				}
+			case ArgMatrix:
+				lhs = tableArray
+			}
+			if compareFormulaArg(lhs, lookupValue, false, exactMatch) == criteriaEq {
+				matchIdx = idx
+				wasExact = true
+				break start
+			}
 		}
+	} else {
+		matchIdx, wasExact = vlookupBinarySearch(tableArray, lookupValue)
+	}
+	if matchIdx == -1 {
+		return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
+	}
+	mtx := tableArray.Matrix[matchIdx]
+	if col < 0 || col >= len(mtx) {
+		return newErrorFormulaArg(formulaErrorNA, "VLOOKUP has invalid column index")
+	}
+	if wasExact || !exactMatch {
+		return mtx[col]
+	}
+	return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
+}
+
+// vlookupBinarySearch finds the position of a target value when range lookup
+// is TRUE, if the data of table array can't guarantee be sorted, it will
+// return wrong result.
+func vlookupBinarySearch(tableArray, lookupValue formulaArg) (matchIdx int, wasExact bool) {
+	var low, high, lastMatchIdx int = 0, len(tableArray.Matrix) - 1, -1
+	for low <= high {
+		var mid int = low + (high-low)/2
+		mtx := tableArray.Matrix[mid]
 		lhs := mtx[0]
 		switch lookupValue.Type {
 		case ArgNumber:
@@ -3812,24 +3873,106 @@ start:
 		case ArgMatrix:
 			lhs = tableArray
 		}
-		switch compareFormulaArg(lhs, lookupValue, false, exactMatch) {
-		case criteriaL:
-			matchIdx = idx
-		case criteriaEq:
+		result := compareFormulaArg(lhs, lookupValue, false, false)
+		if result == criteriaEq {
+			matchIdx, wasExact = mid, true
+			return
+		} else if result == criteriaG {
+			high = mid - 1
+		} else if result == criteriaL {
+			matchIdx, low = mid, mid+1
+			if lhs.Value() != "" {
+				lastMatchIdx = matchIdx
+			}
+		} else {
+			return -1, false
+		}
+	}
+	matchIdx, wasExact = lastMatchIdx, true
+	return
+}
+
+// vlookupBinarySearch finds the position of a target value when range lookup
+// is TRUE, if the data of table array can't guarantee be sorted, it will
+// return wrong result.
+func hlookupBinarySearch(row []formulaArg, lookupValue formulaArg) (matchIdx int, wasExact bool) {
+	var low, high, lastMatchIdx int = 0, len(row) - 1, -1
+	for low <= high {
+		var mid int = low + (high-low)/2
+		mtx := row[mid]
+		result := compareFormulaArg(mtx, lookupValue, false, false)
+		if result == criteriaEq {
+			matchIdx, wasExact = mid, true
+			return
+		} else if result == criteriaG {
+			high = mid - 1
+		} else if result == criteriaL {
+			low, lastMatchIdx = mid+1, mid
+		} else {
+			return -1, false
+		}
+	}
+	matchIdx, wasExact = lastMatchIdx, true
+	return
+}
+
+// LOOKUP function performs an approximate match lookup in a one-column or
+// one-row range, and returns the corresponding value from another one-column
+// or one-row range. The syntax of the function is:
+//
+//    LOOKUP(lookup_value,lookup_vector,[result_vector])
+//
+func (fn *formulaFuncs) LOOKUP(argsList *list.List) formulaArg {
+	if argsList.Len() < 2 {
+		return newErrorFormulaArg(formulaErrorVALUE, "LOOKUP requires at least 2 arguments")
+	}
+	if argsList.Len() > 3 {
+		return newErrorFormulaArg(formulaErrorVALUE, "LOOKUP requires at most 3 arguments")
+	}
+	lookupValue := argsList.Front().Value.(formulaArg)
+	lookupVector := argsList.Front().Next().Value.(formulaArg)
+	if lookupVector.Type != ArgMatrix && lookupVector.Type != ArgList {
+		return newErrorFormulaArg(formulaErrorVALUE, "LOOKUP requires second argument of table array")
+	}
+	cols, matchIdx := lookupCol(lookupVector), -1
+	for idx, col := range cols {
+		lhs := lookupValue
+		switch col.Type {
+		case ArgNumber:
+			lhs = lhs.ToNumber()
+			if !col.Boolean {
+				if lhs.Type == ArgError {
+					lhs = lookupValue
+				}
+			}
+		}
+		if compareFormulaArg(lhs, col, false, false) == criteriaEq {
 			matchIdx = idx
-			wasExact = true
-			break start
+			break
 		}
 	}
-	if matchIdx == -1 {
-		return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
+	column := cols
+	if argsList.Len() == 3 {
+		column = lookupCol(argsList.Back().Value.(formulaArg))
 	}
-	mtx := tableArray.Matrix[matchIdx]
-	if col < 0 || col >= len(mtx) {
-		return newErrorFormulaArg(formulaErrorNA, "VLOOKUP has invalid column index")
+	if matchIdx < 0 || matchIdx >= len(column) {
+		return newErrorFormulaArg(formulaErrorNA, "LOOKUP no result found")
 	}
-	if wasExact || !exactMatch {
-		return mtx[col]
+	return column[matchIdx]
+}
+
+// lookupCol extract columns for LOOKUP.
+func lookupCol(arr formulaArg) []formulaArg {
+	col := arr.List
+	if arr.Type == ArgMatrix {
+		col = nil
+		for _, r := range arr.Matrix {
+			if len(r) > 0 {
+				col = append(col, r[0])
+				continue
+			}
+			col = append(col, newEmptyFormulaArg())
+		}
 	}
-	return newErrorFormulaArg(formulaErrorNA, "VLOOKUP no result found")
+	return col
 }
diff --git a/calc_test.go b/calc_test.go
index c72d78b..b71d822 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -10,6 +10,17 @@ import (
 	"github.com/xuri/efp"
 )
 
+func prepareCalcData(cellData [][]interface{}) *File {
+	f := NewFile()
+	for r, row := range cellData {
+		for c, value := range row {
+			cell, _ := CoordinatesToCellName(c+1, r+1)
+			f.SetCellValue("Sheet1", cell, value)
+		}
+	}
+	return f
+}
+
 func TestCalcCellValue(t *testing.T) {
 	cellData := [][]interface{}{
 		{1, 4, nil, "Month", "Team", "Sales"},
@@ -22,17 +33,6 @@ func TestCalcCellValue(t *testing.T) {
 		{nil, nil, nil, "Feb", "South 1", 32080},
 		{nil, nil, nil, "Feb", "South 2", 45500},
 	}
-	prepareData := func() *File {
-		f := NewFile()
-		for r, row := range cellData {
-			for c, value := range row {
-				cell, _ := CoordinatesToCellName(c+1, r+1)
-				assert.NoError(t, f.SetCellValue("Sheet1", cell, value))
-			}
-		}
-		return f
-	}
-
 	mathCalc := map[string]string{
 		"=2^3":  "8",
 		"=1=1":  "TRUE",
@@ -562,18 +562,28 @@ func TestCalcCellValue(t *testing.T) {
 		"=CHOOSE(1,\"red\",\"blue\",\"green\",\"brown\")": "red",
 		"=SUM(CHOOSE(A2,A1,B1:B2,A1:A3,A1:A4))":           "9",
 		// HLOOKUP
-		"=HLOOKUP(D2,D2:D8,1,FALSE)": "Jan",
-		"=HLOOKUP(F3,F3:F8,3,FALSE)": "34440", // should be Feb
+		"=HLOOKUP(D2,D2:D8,1,FALSE)":          "Jan",
+		"=HLOOKUP(F3,F3:F8,3,FALSE)":          "34440",
+		"=HLOOKUP(INT(F3),F3:F8,3,FALSE)":     "34440",
+		"=HLOOKUP(MUNIT(1),MUNIT(1),1,FALSE)": "1",
 		// VLOOKUP
-		"=VLOOKUP(D2,D:D,1,FALSE)":           "Jan",
-		"=VLOOKUP(D2,D:D,1,TRUE)":            "Month", // should be Feb
-		"=VLOOKUP(INT(36693),F2:F2,1,FALSE)": "36693",
-		"=VLOOKUP(INT(F2),F3:F9,1)":          "32080",
-		"=VLOOKUP(MUNIT(3),MUNIT(2),1)":      "0", // should be 1
-		"=VLOOKUP(MUNIT(3),MUNIT(3),1)":      "1",
+		"=VLOOKUP(D2,D:D,1,FALSE)":            "Jan",
+		"=VLOOKUP(D2,D1:D10,1)":               "Jan",
+		"=VLOOKUP(D2,D1:D11,1)":               "Feb",
+		"=VLOOKUP(D2,D1:D10,1,FALSE)":         "Jan",
+		"=VLOOKUP(INT(36693),F2:F2,1,FALSE)":  "36693",
+		"=VLOOKUP(INT(F2),F3:F9,1)":           "32080",
+		"=VLOOKUP(INT(F2),F3:F9,1,TRUE)":      "32080",
+		"=VLOOKUP(MUNIT(3),MUNIT(3),1)":       "0",
+		"=VLOOKUP(A1,A3:B5,1)":                "0",
+		"=VLOOKUP(MUNIT(1),MUNIT(1),1,FALSE)": "1",
+		// LOOKUP
+		"=LOOKUP(F8,F8:F9,F8:F9)":      "32080",
+		"=LOOKUP(F8,F8:F9,D8:D9)":      "Feb",
+		"=LOOKUP(1,MUNIT(1),MUNIT(1))": "1",
 	}
 	for formula, expected := range mathCalc {
-		f := prepareData()
+		f := prepareCalcData(cellData)
 		assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
 		result, err := f.CalcCellValue("Sheet1", "C1")
 		assert.NoError(t, err, formula)
@@ -759,8 +769,9 @@ func TestCalcCellValue(t *testing.T) {
 		// MULTINOMIAL
 		`=MULTINOMIAL("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax",
 		// _xlfn.MUNIT
-		"=_xlfn.MUNIT()":    "MUNIT requires 1 numeric argument",                 // not support currently
-		`=_xlfn.MUNIT("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax", // not support currently
+		"=_xlfn.MUNIT()":    "MUNIT requires 1 numeric argument",
+		`=_xlfn.MUNIT("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax",
+		"=_xlfn.MUNIT(-1)":  "",
 		// ODD
 		"=ODD()":    "ODD requires 1 numeric argument",
 		`=ODD("X")`: "strconv.ParseFloat: parsing \"X\": invalid syntax",
@@ -947,10 +958,15 @@ func TestCalcCellValue(t *testing.T) {
 		"=VLOOKUP(ISNUMBER(1),F3:F9,1)":  "VLOOKUP no result found",
 		"=VLOOKUP(INT(1),E2:E9,1)":       "VLOOKUP no result found",
 		"=VLOOKUP(MUNIT(2),MUNIT(3),1)":  "VLOOKUP no result found",
-		"=VLOOKUP(A1:B2,B2:B3,1)":        "VLOOKUP no result found",
+		"=VLOOKUP(1,G1:H2,1,FALSE)":      "VLOOKUP no result found",
+		// LOOKUP
+		"=LOOKUP()":                     "LOOKUP requires at least 2 arguments",
+		"=LOOKUP(D2,D1,D2)":             "LOOKUP requires second argument of table array",
+		"=LOOKUP(D2,D1,D2,FALSE)":       "LOOKUP requires at most 3 arguments",
+		"=LOOKUP(D1,MUNIT(1),MUNIT(1))": "LOOKUP no result found",
 	}
 	for formula, expected := range mathCalcError {
-		f := prepareData()
+		f := prepareCalcData(cellData)
 		assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
 		result, err := f.CalcCellValue("Sheet1", "C1")
 		assert.EqualError(t, err, expected, formula)
@@ -974,7 +990,7 @@ func TestCalcCellValue(t *testing.T) {
 		"=A1/A2/SUM(A1:A2:B1)*A3":         "0.125",
 	}
 	for formula, expected := range referenceCalc {
-		f := prepareData()
+		f := prepareCalcData(cellData)
 		assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
 		result, err := f.CalcCellValue("Sheet1", "C1")
 		assert.NoError(t, err)
@@ -988,7 +1004,7 @@ func TestCalcCellValue(t *testing.T) {
 		"=1+SUM(SUM(A1+A2/A4)*(2-3),2)": "#DIV/0!",
 	}
 	for formula, expected := range referenceCalcError {
-		f := prepareData()
+		f := prepareCalcData(cellData)
 		assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
 		result, err := f.CalcCellValue("Sheet1", "C1")
 		assert.EqualError(t, err, expected)
@@ -1000,23 +1016,23 @@ func TestCalcCellValue(t *testing.T) {
 		"=RANDBETWEEN(1,2)",
 	}
 	for _, formula := range volatileFuncs {
-		f := prepareData()
+		f := prepareCalcData(cellData)
 		assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula))
 		_, err := f.CalcCellValue("Sheet1", "C1")
 		assert.NoError(t, err)
 	}
 
 	// Test get calculated cell value on not formula cell.
-	f := prepareData()
+	f := prepareCalcData(cellData)
 	result, err := f.CalcCellValue("Sheet1", "A1")
 	assert.NoError(t, err)
 	assert.Equal(t, "", result)
 	// Test get calculated cell value on not exists worksheet.
-	f = prepareData()
+	f = prepareCalcData(cellData)
 	_, err = f.CalcCellValue("SheetN", "A1")
 	assert.EqualError(t, err, "sheet SheetN is not exist")
 	// Test get calculated cell value with not support formula.
-	f = prepareData()
+	f = prepareCalcData(cellData)
 	assert.NoError(t, f.SetCellFormula("Sheet1", "A1", "=UNSUPPORT(A1)"))
 	_, err = f.CalcCellValue("Sheet1", "A1")
 	assert.EqualError(t, err, "not support UNSUPPORT function")
@@ -1036,24 +1052,13 @@ func TestCalculate(t *testing.T) {
 	assert.EqualError(t, calculate(opd, opt), err)
 }
 
-func TestCalcCellValueWithDefinedName(t *testing.T) {
+func TestCalcWithDefinedName(t *testing.T) {
 	cellData := [][]interface{}{
 		{"A1 value", "B1 value", nil},
 	}
-	prepareData := func() *File {
-		f := NewFile()
-		for r, row := range cellData {
-			for c, value := range row {
-				cell, _ := CoordinatesToCellName(c+1, r+1)
-				assert.NoError(t, f.SetCellValue("Sheet1", cell, value))
-			}
-		}
-		assert.NoError(t, f.SetDefinedName(&DefinedName{Name: "defined_name1", RefersTo: "Sheet1!A1", Scope: "Workbook"}))
-		assert.NoError(t, f.SetDefinedName(&DefinedName{Name: "defined_name1", RefersTo: "Sheet1!B1", Scope: "Sheet1"}))
-
-		return f
-	}
-	f := prepareData()
+	f := prepareCalcData(cellData)
+	assert.NoError(t, f.SetDefinedName(&DefinedName{Name: "defined_name1", RefersTo: "Sheet1!A1", Scope: "Workbook"}))
+	assert.NoError(t, f.SetDefinedName(&DefinedName{Name: "defined_name1", RefersTo: "Sheet1!B1", Scope: "Sheet1"}))
 	assert.NoError(t, f.SetCellFormula("Sheet1", "C1", "=defined_name1"))
 	result, err := f.CalcCellValue("Sheet1", "C1")
 	assert.NoError(t, err)
@@ -1061,7 +1066,7 @@ func TestCalcCellValueWithDefinedName(t *testing.T) {
 	assert.Equal(t, "B1 value", result, "=defined_name1")
 }
 
-func TestCalcPow(t *testing.T) {
+func TestCalcArithmeticOperations(t *testing.T) {
 	err := `strconv.ParseFloat: parsing "text": invalid syntax`
 	assert.EqualError(t, calcPow("1", "text", nil), err)
 	assert.EqualError(t, calcPow("text", "1", nil), err)
@@ -1085,7 +1090,7 @@ func TestCalcPow(t *testing.T) {
 	assert.EqualError(t, calcDiv("text", "1", nil), err)
 }
 
-func TestISBLANK(t *testing.T) {
+func TestCalcISBLANK(t *testing.T) {
 	argsList := list.New()
 	argsList.PushBack(formulaArg{
 		Type: ArgUnknown,
@@ -1096,7 +1101,7 @@ func TestISBLANK(t *testing.T) {
 	assert.Empty(t, result.Error)
 }
 
-func TestAND(t *testing.T) {
+func TestCalcAND(t *testing.T) {
 	argsList := list.New()
 	argsList.PushBack(formulaArg{
 		Type: ArgUnknown,
@@ -1107,7 +1112,7 @@ func TestAND(t *testing.T) {
 	assert.Empty(t, result.Error)
 }
 
-func TestOR(t *testing.T) {
+func TestCalcOR(t *testing.T) {
 	argsList := list.New()
 	argsList.PushBack(formulaArg{
 		Type: ArgUnknown,
@@ -1118,7 +1123,7 @@ func TestOR(t *testing.T) {
 	assert.Empty(t, result.Error)
 }
 
-func TestDet(t *testing.T) {
+func TestCalcDet(t *testing.T) {
 	assert.Equal(t, det([][]float64{
 		{1, 2, 3, 4},
 		{2, 3, 4, 5},
@@ -1127,7 +1132,12 @@ func TestDet(t *testing.T) {
 	}), float64(0))
 }
 
-func TestCompareFormulaArg(t *testing.T) {
+func TestCalcToBool(t *testing.T) {
+	b := newBoolFormulaArg(true).ToBool()
+	assert.Equal(t, b.Boolean, true)
+	assert.Equal(t, b.Number, 1.0)
+}
+func TestCalcCompareFormulaArg(t *testing.T) {
 	assert.Equal(t, compareFormulaArg(newEmptyFormulaArg(), newEmptyFormulaArg(), false, false), criteriaEq)
 	lhs := newListFormulaArg([]formulaArg{newEmptyFormulaArg()})
 	rhs := newListFormulaArg([]formulaArg{newEmptyFormulaArg(), newEmptyFormulaArg()})
@@ -1141,9 +1151,82 @@ func TestCompareFormulaArg(t *testing.T) {
 	assert.Equal(t, compareFormulaArg(formulaArg{Type: ArgUnknown}, formulaArg{Type: ArgUnknown}, false, false), criteriaErr)
 }
 
-func TestMatchPattern(t *testing.T) {
+func TestCalcMatchPattern(t *testing.T) {
 	assert.True(t, matchPattern("", ""))
 	assert.True(t, matchPattern("file/*", "file/abc/bcd/def"))
 	assert.True(t, matchPattern("*", ""))
 	assert.False(t, matchPattern("file/?", "file/abc/bcd/def"))
 }
+
+func TestCalcVLOOKUP(t *testing.T) {
+	cellData := [][]interface{}{
+		{nil, nil, nil, nil, nil, nil},
+		{nil, "Score", "Grade", nil, nil, nil},
+		{nil, 0, "F", nil, "Score", 85},
+		{nil, 60, "D", nil, "Grade"},
+		{nil, 70, "C", nil, nil, nil},
+		{nil, 80, "b", nil, nil, nil},
+		{nil, 90, "A", nil, nil, nil},
+		{nil, 85, "B", nil, nil, nil},
+		{nil, nil, nil, nil, nil, nil},
+	}
+	f := prepareCalcData(cellData)
+	calc := map[string]string{
+		"=VLOOKUP(F3,B3:C8,2)":       "b",
+		"=VLOOKUP(F3,B3:C8,2,TRUE)":  "b",
+		"=VLOOKUP(F3,B3:C8,2,FALSE)": "B",
+	}
+	for formula, expected := range calc {
+		assert.NoError(t, f.SetCellFormula("Sheet1", "F4", formula))
+		result, err := f.CalcCellValue("Sheet1", "F4")
+		assert.NoError(t, err, formula)
+		assert.Equal(t, expected, result, formula)
+	}
+	calcError := map[string]string{
+		"=VLOOKUP(INT(1),C3:C3,1,FALSE)": "VLOOKUP no result found",
+	}
+	for formula, expected := range calcError {
+		assert.NoError(t, f.SetCellFormula("Sheet1", "F4", formula))
+		result, err := f.CalcCellValue("Sheet1", "F4")
+		assert.EqualError(t, err, expected, formula)
+		assert.Equal(t, "", result, formula)
+	}
+}
+
+func TestCalcHLOOKUP(t *testing.T) {
+	cellData := [][]interface{}{
+		{"Example Result Table"},
+		{nil, "A", "B", "C", "E", "F"},
+		{"Math", .58, .9, .67, .76, .8},
+		{"French", .61, .71, .59, .59, .76},
+		{"Physics", .75, .45, .39, .52, .69},
+		{"Biology", .39, .55, .77, .61, .45},
+		{},
+		{"Individual Student Score"},
+		{"Student:", "Biology Score:"},
+		{"E"},
+	}
+	f := prepareCalcData(cellData)
+	formulaList := map[string]string{
+		"=HLOOKUP(A10,A2:F6,5,FALSE)":  "0.61",
+		"=HLOOKUP(D3,D3:D3,1,TRUE)":    "0.67",
+		"=HLOOKUP(F3,D3:F3,1,TRUE)":    "0.8",
+		"=HLOOKUP(A5,A2:F2,1,TRUE)":    "F",
+		"=HLOOKUP(\"D\",A2:F2,1,TRUE)": "C",
+	}
+	for formula, expected := range formulaList {
+		assert.NoError(t, f.SetCellFormula("Sheet1", "B10", formula))
+		result, err := f.CalcCellValue("Sheet1", "B10")
+		assert.NoError(t, err, formula)
+		assert.Equal(t, expected, result, formula)
+	}
+	calcError := map[string]string{
+		"=HLOOKUP(INT(1),A3:A3,1,FALSE)": "HLOOKUP no result found",
+	}
+	for formula, expected := range calcError {
+		assert.NoError(t, f.SetCellFormula("Sheet1", "B10", formula))
+		result, err := f.CalcCellValue("Sheet1", "B10")
+		assert.EqualError(t, err, expected, formula)
+		assert.Equal(t, "", result, formula)
+	}
+}