VLOOKUP referencing another worksheet returning "#NAME?" instead of the correct lookup value

Question

It doesn't seem like VLOOKUP is working in the React spreadsheet component. I have a simple lookup to another sheet in the workbook, but when the spreadsheet renders, the cell value is "#NAME?" instead of the expected vlookup resolved value.

Note: simple formulas like MAX(), MIN() seem to work.

I've attached a simple spreadsheet illustrating: Appendix sheet contains the target of the VLOOKUP. It works when loading in Google Sheet.

Tim Law · Answer

Actually, looks like the issue is with the usage of the "FALSE()" function in the VLOOKUP definition.

Formula looks like:

=VLOOKUP(A3,Appendix!$A$2:$B$4, 2, FALSE())

If I remove "FALSE()" then it works.

I guess different question now: is it possible to support the FALSE() (and I suppose TRUE()) functions?

Janakiraman Sakthivel · Answer

Hi Tim Law,

We have reviewed the issue you reported with the "VLOOKUP()"
function and would like to inform you that, unfortunately, our Spreadsheet
currently does not support the FALSE() and TRUE() formulas. As a result,
when using FALSE() or TRUE() formulas in the VLOOKUP()
function instead of direct Boolean values, the issue you mentioned occurs.However, our Spreadsheet provides the addCustomFunction() method, which allows you to add custom functions based on your specific
requirements at the sample level. Using this method, you can include the TRUE() and FALSE() formulas as
custom functions in the Spreadsheet component. Once added, you can utilize these custom TRUE()
and FALSE() functions seamlessly within the =VLOOKUP() formula.

Please refer to the links below for more information regarding this function.
Documentation link: https://ej2.syncfusion.com/react/documentation/spreadsheet/formulas#create-user-defined-functions--custom-functions
API link: https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#addcustomfunction

For your convenience, we have prepared a sample in which the TRUE() and FALSE()
formulas are added as custom functions. In this sample, we have included the =VLOOKUP()
formula in cell "H4", referencing another worksheet as you
described. Additionally, the custom FALSE() function has been used
within this formula.

Sample: Xnu4p4hu (forked) -
StackBlitz

CODE SNIPPET:

// This event will be triggered once the spreadsheet component is rendered.
  const onCreated = () => {
          // Added custom functions for the TRUE and FALSE formulas.
          spreadsheet.addCustomFunction(trueHandler, 'TRUE');
          spreadsheet.addCustomFunction(falseHandler, 'FALSE');
   
          spreadsheet.updateCell({ formula: "=VLOOKUP(H3,'Sheet2'!C2:F8,3,FALSE())" }, 'H4');
   
      };
  
      const trueHandler = (...args) => {
          if (args.length > 1 || (args.length === 1 && args[0] !== "")) {
              throw new Error('#VALUE!');
          }
          return true;
      }
   
      const falseHandler = (...args) => {
          if (args.length > 1 || (args.length === 1 && args[0] !== "")) {
              throw new Error('#VALUE!');
          }
          return false;
      }

Please refer to the links below to know about the APIs used in the shared
sample above.
https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#created
https://ej2.syncfusion.com/react/documentation/api/spreadsheet/#updatecell

Kindly check the sample and details shared above on your end and get back to us
if you need further clarification regarding this. We hope this helps.

Additionally, we have planned to include the functions "TRUE()"
and "FALSE()" in our spreadsheet, and we have confirmed this
as an improvement, logging it as a feature. It will be available in one of our
upcoming releases. You can communicate and track the status of this feature
using the link below from our feedback portal.

Feedback links for tracking purposes: https://www.syncfusion.com/feedback/42091/need-to-include-the-listed-unsupported-formula-into-spreadsheet-component At the planning stage for every release cycle, we
review all the open features once again and finalize features for
implementation based on specific parameters including product vision,
technological feasibility, and customer interest. Once we have anything
definite to share about these features implementation, we will move the
feedback to scheduled status with the tentative release timeline. We appreciate
your patience until then.

Tim Law · Answer

Yup, I'm already using the addCustomFunction​ function as a fallback, so I should be covered until TRUE() / FALSE() make it into the core product

Janakiraman Sakthivel · Answer

Hi Tim Law,

As promised, we will include the "TRUE()" and "FALSE()"
functions in the spreadsheet in one of our upcoming releases. Until then, you
can track the status of this feature using the shared feedback link. Once these
functions are included, we will notify you. Thank you for your patience in the
meantime.