Interop Excel .Find() whole match not finding value, partial match does

Question

So I've got a spreadsheet where I'm trying to find the index of certain column headers. What I've found is XlLookAt.xlWhole does not find the value. However, if I use XlLookAt.xlPartit does.

I cannot use xlPart as it does not find the correct match in some instances.

I have confirmed that only AMT_ISSUED is in the cell in the actual spreadsheet and there aren't any white spaces on either end.

Does anyone know why XlLookAt.xlWhole doesn't work . Here's the code I'm using

List<int> columnNumbers = new List<int>();
object misValue = System.Reflection.Missing.Value;
var columnIndex = range.EntireRow.Find("AMT_ISSUED",
                            misValue, XlFindLookIn.xlValues, XlLookAt.xlWhole,
                            XlSearchOrder.xlByColumns, XlSearchDirection.xlNext,
                            false);

var index = columnIndex?.Column ?? 0;
columnNumbers.Add(index);

UPDATE:

I have even done this:

 var value = ((Range)range.Cells[1, 4]).Value2.ToString();

 var columnIndex = range.EntireRow.Find(value,
                        misValue, XlFindLookIn.xlValues, XlLookAt.xlWhole,
                        XlSearchOrder.xlByColumns, XlSearchDirection.xlNext,
                        false);
 var index = columnIndex?.Column ?? 0;
 columnNumbers.Add(index);

value has found the correct text but column index is still null.

WORKAROUND (I don't like it but it will get me past this hurdle) Note: the headings could be in row 1 or 2, if it is in row 2 then row one has the first cell populated

for (int i = 1; i < 3; i++)
            {
                for (var h = 1; h <= colCount; h++)
                {
                    object cellValue = ((Range)range.Cells[i, h]).Value2;
                    if ((h== 1 || h==2) && cellValue == null)
                    {
                        break;
                    }

                    if (columns.Contains(cellValue))
                    {
                        columnNumbers.Add(h);
                    }
                }
            }

Answer 1

The Range.Find method returns Range object. So, first, you need to obtain the Range and then, if it's not null, retrieve column index:

// Create Excel instance
Excel.Application excel = new Excel.Application { Visible = true };
Excel.Workbook book = excel.Workbooks.Open(@"PATH_TO_FILE");
Excel.Worksheet sheet = book.Sheets[1] as Excel.Worksheet;
// Search in the first row
Excel.Range header = sheet.Range["1:1"].Find("AMT_ISSUED", LookAt: Excel.XlLookAt.xlWhole);
if (header != null)
{
    // Header is found
    int index = header.Column;
}
else
{
    // Header is not found
}