topic Re: JMP Import: "XLookup" equation in Excel file in Discussions

JMP Import: "XLookup" equation in Excel file

hogi — Sat, 10 Jun 2023 23:34:34 GMT

Great,

in Excel 365 there is a new "XLookup/XVerweis" *) function
https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

But doesn't seem to understand it (yet). Same at your site?

*) In contrast to the old VLookup/Sverweis, for XLookup the column with the return values can now also be in front of the search values.

No re-shuffling of columns anymore ;)

Re: JMP Import: "XLookup" equation in Excel file

Craige_Hales — Mon, 02 Aug 2021 14:25:05 GMT

If you want to build JSL to do lookups, JMP's associative array is good for mapping IDs to birthdays (no interpolation would make sense). JMP's interpolate(...) function is good for sorted lookup tables, with interpolation, mapping time to temperature, for example.

Re: JMP Import: "XLookup" equation in Excel file

jthi — Mon, 02 Aug 2021 14:46:05 GMT

Managed to replicate this with JMP16.0. JMP will return #NAME? for cells with XLOOKUP formula. Attached is Excel sheet with three different formulas: XLOOKUP, SUM and LOOKUP in which SUM and LOOKUP work fine. I would guess that this will be fixed in future version of JMP (but no idea in which).

For a workaround you could recalculate the values after importing the data from excel with JMP formula.

Re: JMP Import: "XLookup" equation in Excel file

hogi — Tue, 03 Aug 2021 08:29:03 GMT

The issue is fixed in JMP 16.1.

I just updated my installation and could confirm: working now.