topic Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel? in Discussions

Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

PeppeCanta — Sat, 10 Jun 2023 23:20:55 GMT

Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

This is the table I am working on:

In the column “Result” I want to put a particular value which respond to this rule:

If (“Y” is not 0) Return the “X” value contained in the cell with the same “NAME” as the current cell and with “ID” equal to the index indicated in “Y” (in this case 5).
Basically in the first row I want that “Result” contains “4.7”

@txnelson you are the JMP wizard :) Do you believe it is possible?

Cheers

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

txnelson — Wed, 21 Oct 2020 15:56:38 GMT

Here is the formula I came up with

curName = :NAME;
curY = :Y;
theResult = 0;
If( :Y != 0,
	theRows = Current Data Table() << get rows where( :NAME == curName & :ID == curY );
	If( Is Missing( theRows ) != 1,
		theResult = :X[theRows]
	);
,
	theResult = 0
);
theResult;

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

dexue — Tue, 27 Jul 2021 14:28:50 GMT

Hi All, i need expert advise on it. Currently i working on Index Match with using the same method / formula that you guys provided. I got return value of "0".

My data as shown below. Hope someone can help me on this.

* ID & Y column data from time converted to seconds.

Formula:

curY = :Y;
theResult = 0;
If( :Y != 0,
	theRows = Current Data Table() << get rows where( :ID == curY );
	If( Is Missing( theRows ) != 1,
		theResult = :X[theRows]
	);
,
	theResult = 0
);
theResult;

Thanks!

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

txnelson — Tue, 27 Jul 2021 14:44:13 GMT

The results I get when running against your Example data table, using your supplied formula is:

I have attached the Example data table, with the formula applied

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

dexue — Wed, 28 Jul 2021 02:58:52 GMT

Thanks Jim! I found that my file not working because column ID with decimal point and Y column without decimal point.

I had tried another set data which with date in the column ID and Y (Y with formula 1st cell add 5sec of start time and subsequent cell increment of 1second) as below. But I still getting "0" for result column. I need your advice on it. I'm appreciate of your help and guide. Attached with my example. Thanks and appreciate!

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

txnelson — Wed, 28 Jul 2021 03:37:07 GMT

If you do a full display of the ID and Y columns in your supplied data table, you will see that except for the 1st row, all of the subsequent rows have a decimal value of .967.

The Y column has no such decimal value, so the values between them will never match. I believe that you need to determine if the .967 needs to be rounded up, or removed, and then perform that operation to adjust your data, and then create the formula column.

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

dexue — Wed, 28 Jul 2021 13:00:10 GMT

Great ,Thanks Jim! It works after I rounded up column "ID". Finally i got what I want.

But I still don't understand the logic that datetime converted to numeric. Although the time (ID & Y) both are similar.

I tried to copy another set of data which included measurement value (T Temperature) and date (TDate), time (T Measure Time) from Text file to excel file and to the JMP data table, Column 13 (Result) shown failed. I checked the numeric number (T time vs TMeasure Time) which different. Do you have any idea on this? Thanks and appreciate for your guidance!

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

txnelson — Wed, 28 Jul 2021 13:20:14 GMT

You are indicating the data are coming in from Excel. What method are you using to get the data from Excel to JMP? Problems can come from the way Excel saves values and also from JMP dealing with Excel.

I am not an Excel expert, but if you can provide the steps you are taking from text to Excel to JMP and how you are performing those steps, and maybe even samples of the text, Excel and JMP files, the Discussion Community members maybe able to help.

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

dexue — Thu, 12 Aug 2021 11:19:24 GMT

Good Day Jim, I had figure out the problem and solved it. The problem was due to the round second and date. Thanks for your advice.

Would like to ask, how to come out with the formula in JMP that using Excel formula =IF(ISERROR(VLOOKUP(B3,A3:A13,1,FALSE)),"",A3)

Example as below. Appreciate and thanks!

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

txnelson — Thu, 12 Aug 2021 13:33:32 GMT

An exact match for your VLOOKUP would be

Try( (Current Data Table() << get rows where( If(Row()<=13,:Time,.) == :Record Time[1] ))[1], :Time[1] );

However, what I believe you want, is to handle each row, the formula would be

Try( (Current Data Table() << get rows where( :Time == :Record Time[Row()] ))[1], :Time[Row()] );

I did not test the formula since you did not supply a sample data table. So, there might be a minor change needed to get this to work. However, the method used should guide you in how to handle items such as this. Also, if you respond with a sample table, I will test my formula.

Re: Is it possible in JMP to re-create the "INDEX(MATCH()) formula combination available in Excel?

dexue — Thu, 12 Aug 2021 14:11:54 GMT

Great! Thanks Jim, modified a bit and works fine.