Index match does not work with referenced cells

Aspose.Cells Product Family
1

Can you tell me if a formula like this should work:


=INDEX(Parts!A:E,MATCH(HEADER!$B$5&HEADER!$B$3&HEADER!$B$14&$B11,Parts!A:A&Parts!B:B&Parts!C:C&Parts!D:D,0),5)

I use that formula to lookup a value in a table. I tried making a variation of it using VLOOKUP, but that didn’t work either. It seems like this breaks down when I combine cells from more than one cell into a single string using the “&” character.



2

See the attached jpeg. When I convert the excel spreadsheet to an image, this is what I get “#N/A”.


How do I get it to evaluate?
3

Hi Matthew,


First of all, please accept our apologies for the delayed response.

We have evaluated your presented scenario on our end while using the latest version of Aspose.Cells for .NET 8.0.0.2. The resultant image contains #N/A for several cells therefore we have logged the problem in our bug tracking system to probe further into this matter, and to provide a fix if applicable. The ticket Id for your future reference is CELLSNET-42547.

Please note, most of the cells containing the INDEX formula are calculated fine before rendering the worksheet to image except A9, A10, A21, A25, A26, A27 & A30. Instead of calculated value, the rendered image show #N/A for aforesaid cells.

C#

var book = new Aspose.Cells.Workbook(myDir + “Centrifugal+Pump+Inspection+Report.xlsx”);
book.CalculateFormula();
var sheet = book.Worksheets[“REPORT”];
var options = new ImageOrPrintOptions();
var renderer = new SheetRender(sheet, options);
renderer.ToImage(0, myDir + “output-1.jpg”);
4

I discovered an error in my data. It is not an issue with Aspose.Cells.


Thank you for your consideration.

~Matt
5

Hi Matt,

Thanks for your posting and using Aspose.Cells.

It is good to know that you have figured out this issue. We have closed this ticket now with your comment. Let us know if you encounter any other issue, we will be glad to look into it and help you further.

6

Hi,

Thanks for using Aspose.Cells

Please download and try this fix: Aspose.Cells
for .NET v8.0.0.3 and let us know your feedback.

7

The issues you have found earlier (filed as CELLSNET-42547) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.