XLOOKUP, VLOOKUP, and MATCH fail with #N/A if lookup range starts before worksheet dimension (FromRow / FromCol)

[smad2005]

EPPlus usage

Noncommercial use

Environment

windows

Epplus version

8.6.3

Spreadsheet application

excel

Description

Starting from EPPlus version 8.5.0 (and still present in 8.6.3), lookup functions like XLOOKUP, VLOOKUP, HLOOKUP, and MATCH fail to find values (returning #N/A) if the lookup range starts before the first populated cell of the worksheet (i.e. lookupRange.Address.FromRow < Worksheet.Dimension.FromRow).

Root Cause: In XlookupScanner.cs, the methods GetMaxItemsRow and GetMaxItemsColumns were changed to use GetAddressDimensionAdjusted(0) to support virtual/clamped ranges:

private int GetMaxItemsRow(IRangeInfo lookupRange)
{
    var adjusted = lookupRange.GetAddressDimensionAdjusted(0);
    if (adjusted != null)
    {
        return adjusted.ToRow - adjusted.FromRow + 1;
    }
    // ...
}

If the lookup range starts at row 2 (FromRow = 2), but the first populated cell on the worksheet is in row 5 (Dimension.FromRow = 5), GetAddressDimensionAdjusted(0) returns a clamped range starting at row 5.

Thus, adjusted.ToRow - adjusted.FromRow + 1 calculates a smaller size (e.g. 2 items instead of 5).

However, the scanning loop in FindIndexInternal still accesses values using an offset relative to the original start row (FromRow = 2):

object value = direction == LookupRangeDirection.Vertical ?
    _lookupRange.GetOffset(ix, 0) :
    _lookupRange.GetOffset(0, ix);

Since ix iterates from 0 to maxItems - 1 (clamped size), it only queries rows 2 and 3 (which are empty/null) and completely skips the actual data in rows 5 and 6.

Minimal Reproducible Test Case (MSTest):

[TestMethod]
public void ShouldFindExactMatch_WhenRangeStartsBeforeWorksheetDimension()
{
    // Arrange: Leave rows 1-4 empty. Worksheet dimension will start at Row 5.
    _sheet.Cells[5, 2].Value = "Apple";
    _sheet.Cells[6, 2].Value = "Pear";
    // Lookup range B2:B6 starts at row 2 (before worksheet dimension starts).
    var ri = new RangeInfo(_sheet, _sheet.Cells["B2:B6"]);
    var scanner = new XlookupScanner("Pear", ri, LookupSearchMode.StartingAtFirst, LookupMatchMode.ExactMatch);
    
    // Act
    var ix = scanner.FindIndex();
    
    // Assert: "Pear" is at index 4 (relative to B2). In EPPlus 8.5.0+ this returns -1 instead.
    Assert.AreEqual(4, ix);
}

Where to Place the Test in the EPPlus Repository

Target File Path: src/EPPlusTest/FormulaParsing/Excel/Functions/RefAndLookup/LookupScannerTests.cs
Placement: Add this method inside the LookupScannerTests class (e.g. at the very end of the class, just before the closing brackets). The class uses MSTest (Microsoft.VisualStudio.TestTools.UnitTesting), which is why the method is marked with [TestMethod].

[smad2005]

[TestFixture]
    public class XLookupBugTests
    {
        [Test]
        public void XLookup_ShouldFindValue_WhenRangeStartsBeforeWorksheetDimension()
        {
            ExcelPackage.License.SetNonCommercialOrganization("My Noncommercial organization");

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

                // Data starts only from row 5.
                // Therefore, Worksheet.Dimension will start from row 5.
                sheet.Cells["B5"].Value = "Apples";
                sheet.Cells["C5"].Value = "100 USD";

                sheet.Cells["B6"].Value = "Pears";
                sheet.Cells["C6"].Value = "200 USD";

                // Write formula in D5 (not expanding Dimension above row 5)
                sheet.Cells["D5"].Formula = "XLOOKUP(\"Pears\", B2:B6, C2:C6)";

                // Calculate formulas
                sheet.Calculate();

                var result = sheet.Cells["D5"].Value;

                // Assertions:
                Assert.That(result, Is.Not.InstanceOf<ExcelErrorValue>(), "Formula returned an error (likely #N/A)");
                Assert.That(result, Is.EqualTo("200 USD"));
            }
        }
    }