VLOOKUP approximate match returns #N/A when first column has text header + numbers - Includes minimal repro code

[nfcampos]

EPPlus usage

Personal use

Environment

macos, linux

Epplus version

8.3.1

Spreadsheet application

Excel

Description

EPPlus Bug: VLOOKUP approximate match fails with text header row

Summary

VLOOKUP with approximate match (range_lookup=TRUE or omitted) returns #N/A when the first column contains a text value in the first row followed by numeric values, even when the lookup value exists in the data.

Affected Versions

• EPPlus 8.3.1 (confirmed)
• Likely affects earlier versions as well

Reproduction

using OfficeOpenXml;

ExcelPackage.License.SetNonCommercialPersonal("Demo");

using var package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("Sheet1");

// Data with text header in first row
ws.Cells["A1"].Value = "Header";  // Text
ws.Cells["A2"].Value = 1;         // Number
ws.Cells["A3"].Value = 2;
ws.Cells["A4"].Value = 3;
ws.Cells["A5"].Value = 4;
ws.Cells["B1"].Value = "Result";
ws.Cells["B2"].Value = "Found1";
ws.Cells["B3"].Value = "Found2";
ws.Cells["B4"].Value = "Found3";
ws.Cells["B5"].Value = "Found4";

// BUG: Returns #N/A (should return "Found1")
var bug = ws.Calculate("VLOOKUP(1,A1:B5,2,TRUE)");

// WORKS: Returns "Found1"
var ok1 = ws.Calculate("VLOOKUP(1,A1:B5,2,FALSE)");  // exact match
var ok2 = ws.Calculate("VLOOKUP(1,A2:B5,2,TRUE)");   // exclude header

Expected vs Actual

Formula	Expected	Actual	Status
VLOOKUP(1,A1:B5,2,TRUE)	Found1	#N/A	BUG
VLOOKUP(1,A1:B5,2,FALSE)	Found1	Found1	OK
VLOOKUP(1,A2:B5,2,TRUE)	Found1	Found1	OK

[karlkallman]

Hello,

Thank you for discovering this. It was indeed a bug on our end. I've investigated the issue and committed a fix for this.

[nfcampos]

Thanks for the quick fix!