VLOOKUP and XLOOKUP match a substring of a cell instead of the whole cell #2411

Description

compareFormulaArg compares the lookup value with matchPattern, which reports any occurrence of the pattern inside the cell (calc.go:15255). That is what FIND and SEARCH need, but the lookup functions have to match the whole cell. So VLOOKUP("-001", ..., FALSE) matches a cell "46027-001", and an empty lookup value matches the first row, because an empty string is a substring of everything.

There is no error. The formula returns a value from the wrong row.

The same class of bug was fixed for the SUMIF/COUNTIF criteria in #2267 by anchoring the pattern. compareFormulaArg was not touched there.

Steps to reproduce the issue

package main

import (
	"fmt"

	"github.com/xuri/excelize/v2"
)

func main() {
	f := excelize.NewFile()
	for i, key := range []string{"45665-003", "46027-001", "46034-006"} {
		cell, _ := excelize.CoordinatesToCellName(1, i+1)
		f.SetCellValue("Sheet1", cell, key)
		cell, _ = excelize.CoordinatesToCellName(2, i+1)
		f.SetCellValue("Sheet1", cell, (i+1)*11)
	}
	for _, formula := range []string{
		`=VLOOKUP("-001",A1:B3,2,FALSE)`,
		`=VLOOKUP("46027",A1:B3,2,FALSE)`,
		`=VLOOKUP("",A1:B3,2,FALSE)`,
		`=XLOOKUP("-001",A1:A3,B1:B3,"NF",2)`,
	} {
		f.SetCellFormula("Sheet1", "D1", formula)
		result, err := f.CalcCellValue("Sheet1", "D1")
		fmt.Printf("%-38s %-8q %v\n", formula, result, err)
	}
}

Describe the results you received

=VLOOKUP("-001",A1:B3,2,FALSE)         "22"     <nil>
=VLOOKUP("46027",A1:B3,2,FALSE)        "22"     <nil>
=VLOOKUP("",A1:B3,2,FALSE)             "11"     <nil>
=XLOOKUP("-001",A1:A3,B1:B3,"NF",2)    "22"     <nil>

Describe the results you expected

=VLOOKUP("-001",A1:B3,2,FALSE)         "#N/A"   #N/A
=VLOOKUP("46027",A1:B3,2,FALSE)        "#N/A"   #N/A
=VLOOKUP("",A1:B3,2,FALSE)             "#N/A"   #N/A
=XLOOKUP("-001",A1:A3,B1:B3,"NF",2)    "NF"     <nil>

VLOOKUP("46027*", ...) and VLOOKUP("*-001", ...) have to keep returning 22. FIND and SEARCH have to keep reporting any occurrence.

Go version

go version go1.26.8 linux/amd64

Excelize version or commit ID

0434413

Environment

GOOS='linux'
GOARCH='amd64'
CGO_ENABLED='1'
GOVERSION='go1.26.8'