IF conditions inside HLOOKUP don't work

[Suranga R]

Hi,

Following does not work with XlsIO, but works in MS Excel.

HLOOKUP(A2,IF(A4=1,'Lookup'!AH2:BL49,IF(A4=2,'Lookup'!A2:AE49)),A1-16,FALSE)

If I modified above as follows, it works.

IF(A4=1,HLOOKUP(A2,'Lookup'!AH2:BL49,A1-16,FALSE),HLOOKUP(A2,'Lookup'!A2:AE49,A1-16,FALSE))

Also found the following issue as well with DAYS360 function

DAYS360(x,y)                             - Working

DAYS360(x,y, FALSE)               - Not Working (But works with MS Excel)

DAYS360(x,y, TRUE)                - Working

Appreciate very much any help on this.

I'm using Essential Studio 15.3.0.26 (If there is a known issue in this version and it is fixed in a later release, appreciate if you can tell me the minimum version it works)

Excel is created using MS Office Excel 2013.

If there are limitations with the formulas, where can I find its documentation.

Thnaks,

- Suranga

[Abirami Varadharajan]

Hi Suranga, 

 

Thank you for contacting syncfusion support. 

 

Please find the details below. 

 

Queries	Details
Following does not work with XlsIO, but works in MS Excel.  HLOOKUP(A2,IF(A4=1,'Lookup'!AH2:BL49,IF(A4=2,'Lookup'!A2:AE49)),A1-16,FALSE)    If I modified above as follows, it works.  IF(A4=1,HLOOKUP(A2,'Lookup'!AH2:BL49,A1-16,FALSE),HLOOKUP(A2,'Lookup'!A2:AE49,A1-16,FALSE))	We tried to reproduce possible cases in HVLOOKUP formula at our end. But unfortunately, we are unable to replicate the issue. So, we request you to share issue reproducing sample or input template used at your end which will be helpful for us to provide prompt solution at the earliest.   The document we checked is attached for your reference: http://www.syncfusion.com/downloads/support/directtrac/general/ze/HLOOKUP111380178.zip
DAYS360(x,y)                             - Working  DAYS360(x,y, FALSE)               - Not Working (But works with MS Excel)  DAYS360(x,y, TRUE)                - Working	We are able to reproduce the reported ”Issue in Calculating DAYS360 formula when the argument method as false in CalcEngine”. We have logged a defect report for that issue. The fix for this issue is estimated to be available on our upcoming release 16.2 SP – 1 which will be available by end of July 2018.

 

Regards, 

Abirami. 

[Suranga R]

Hi Abirami,

This is the exact formula which didn't work with XlsIO.

=IF(OR(C32<10000,C32>200000),"N/A",HLOOKUP(F5,IF(F38=1,'Sh-2'!AH2:BL49,IF(F38=2,'Sh-2'!A2:AE49)),F4-16,FALSE)*C32/10000*(1+D32+E32))

Then we modified above as following.

=IF(OR(C32<10000,C32>200000),"N/A",IF(F38=1,HLOOKUP(F5,'Sh-2'!AH2:BL49,F4-16,FALSE),  HLOOKUP(F5,'Sh-2'!A2:AE49,F4-16,FALSE))*C32/10000*(1+D32+E32))

Modified version works with XlsIO.

Thank you very much,

Suranga.

[Prakash Kumar D]

Hi Suranga, 

 

Thank you updating us. 

 

We can reproduce the reported issue “Calculated value for HLOOKUP formula is incorrect in XlsIO”. We have logged a defect report for that issue. The fix for this issue is estimated to be available on our upcoming release 16.2 SP – 1 which will be available by end of July 2018.  

 

Regards, 

Prakash Kumar