2
Vote

VLOOKUP returns #N/A

description

Hi team.

I have a problem in using "VLOOKUP".

When inputing data directly using Excel, I get the correct value.
But I can't get the correct value from my program.The return value will be "#N/A".

--- from ---
var inputExcelFile = new FileInfo("book1.xlsx");
using (var inputFile = new ExcelPackage(inputExcelFile))
{
var sheet = inputFile.Workbook.Worksheets["Sheet1"];

sheet.Cells["B3"].Value = "ABC_123";
sheet.Calculate("C3");

var value = sheet.Cells["C3"].Value;
}
--- to ---

I debuged a little bit and got why "#N/A" is returned.

If the "table_array" doesn't contain "RichText", I get the correct value.
But, if the "table_array" value in VLOOKUP function has "RichText" (for example, partially set the font to bold), the matching with the lookup_value will be matched with "RichText" and match will be FALSE.

Is my usage is incorrect?
Could anyone help me?

I'll attach a sample file.

I'm japanese.Sorry for my bad English.

file attachments

comments

eladh wrote Mar 5 at 1:42 PM

I have noticed same issue of Vlookup returns N/A.

it is working properly without the "Calculate" Method. and returning the correct value.__

When using the "Calculate" the error appears in case VLookup formula contained only one 3 params.
When i added the 4th param (RANGE_LOOKUP) for exact match (0) - it worked fine.

is it a bug ?