topic Re: Similar functionality as XLOOKUP() from Excel in JMP? in Discussions

Similar functionality as XLOOKUP() from Excel in JMP?

Jo_E — Tue, 05 Aug 2025 13:10:01 GMT

I have a data table where for each measured value the batch (#XXXX, #YYYY, #ZZZZ#), the storage temperature (5, 25 or 40) and the storage time (0, 3, 6, 9, 12) is specified. The idea is to subtract from each measure value the value with a storage time of 0 and the same batch and storage time as the in the row.
I was able to slove this in excel with XLOOKUP(). Is this also possible in JMP?

Re: Similar functionality as XLOOKUP() from Excel in JMP?

hogi — Tue, 05 Aug 2025 13:23:48 GMT

Please have a look at Col ...() aggregation - they are much more powerful than XLookup().

In the Marketplace you can find the Normalization GUI.
It can be used to calculate the difference y - and many other cases ...
https://marketplace.jmp.com/appdetails/Normalization+GUI

Re: Similar functionality as XLOOKUP() from Excel in JMP?

Jo_E — Tue, 05 Aug 2025 13:44:51 GMT

Maybe I am wrong but to my understanding this does not resolve the current issue and there maybe an overview on the Col funtions available?

Re: Similar functionality as XLOOKUP() from Excel in JMP?

Jo_E — Tue, 05 Aug 2025 13:55:54 GMT

This file would be an example for such a table. And for each data set within the table the correct T=0 should be used for calculating the difference and the correct one is the one from the same batch and the same temperature, but also the storage time =0. I hope this makes it a bit clearer.

Re: Similar functionality as XLOOKUP() from Excel in JMP?

jthi — Tue, 05 Aug 2025 14:20:01 GMT

Based on your example data, the smallest Measured Value is always for Storage Time = 0, is this always true? If this is the case you can use Col Min

:Measured Value - Col Min(:Measured Value, :Batch, :Temperture)

And if this isn't always the case you need a bit more complicated formula

:Measured Value - Col Min(If(:Storage Time == 0, :Measured Value, .), :Batch, :Temperture)

Re: Similar functionality as XLOOKUP() from Excel in JMP?

Jo_E — Tue, 05 Aug 2025 14:26:48 GMT

Thank you, that works!