cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
PeppeCanta
Level I

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

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

This is the table I am working on:

Example.JPG

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

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

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;
Jim

View solution in original post

10 REPLIES 10
txnelson
Super User

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

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;
Jim
dexue
Level II

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

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.

dexue_0-1627395040002.png

 

 

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!

txnelson
Super User

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

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

txnelson_0-1627396960733.png

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

 

Jim
dexue
Level II

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

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!

 

dexue_0-1627440753477.png

 

txnelson
Super User

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

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. 

txnelson_0-1627443402451.png

 

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. 

Jim
dexue
Level II

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

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!

 

dexue_0-1627476897599.png

 

txnelson
Super User

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

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.

Jim
dexue
Level II

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

 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!

dexue_0-1628767099793.png

 

txnelson
Super User

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

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.

Jim