Obtaining the row number of a cell that fulfills a certain criterion

[#1]

Is there a function that will return the row number of the (first) cell in a certain column that fulfills a criterion, e.g. begins with "XYZ" or is larger than X. That will allow me to obtain the data of a relevant cell in a different column, similar to the function of the VLOOKUP-function i Excel.

Preferably I want to do it in the Formula editor.

[#2 · Accepted solution]

I usually use Update and Join (or jsl) for VLOOKUP-like work in JMP.

But here's a way to do to it with a column formula:

:height[Minimum(

  Data Table( "Big Class.jmp" ) << get rows where( :name == "ROBERT" )

)]

The example is based on the Big Class.jmp sample data table. It returns the height of the first ROBERT in the name column. Just paste the code into the Formula Editor.

However, note that the table name must explicitly given in the formula for it to work. And if rows are added/deleted or cell values change this type of formulas that use jsl commands not found in the Formula Editor may not automatically update. If so, the "Apply" button in the formula editor needs another click.

[#3]

Thank you. I have been looking for this for a long time!

[#4]

Is there a way to add "By" criteria to this function. That is for example I want to find the row number of the first Cell in Column 1 that equals "X", but I want to do that separately for each value of Column 2?

I can do that with complicated If-functions, but it would be nice if I could do it similarly to the way the Col-prefixed statistical functions work.

[#5]

This seems to work:

r = Current Data Table() << get rows where( :height == 60 );

:name[r[Contains( :sex, :sex )]];

It finds the name of the first person of each sex with height = 60. Not sure if Current Data Table will work under every condition (e.g interferencve with independently run scripts)

[#6]

Thank you. What if I have multiple "By" criteria? And where do I specify whether i searches for the first or last row fulfilling the criteria?

[#7]

The above works for multiple cases in the By column (i.e. not limited ro just "M" and "F" as in the example). But it will not work for several "By-columns" without complicating it too much. I suggest using a new column that combines the categories from multiple columns (e.g "age-sex").

Below is a sligtly modified version that 1) allows choosing the first or last row (change Min to Max to get the last row) and 2) prevents error if no rows would meet the criteria.

If( N Row( r = Data Table( "Big Class.jmp" ) << get rows where( :height == 60 ) ) > 0,

  If( (q = Min( Loc( :sex[r], :sex ) )) > 0,

  :name[r[q]]

  )

)

[#8]

I think I solved it myself using your original suggestion:

a = :By Criteria 1; b = :By Criteria 2; :Column 1[Minimum(Current Data Table() << get rows where(:Column 2 == "X" & :By Criteria 1 == a & :By Criteria 2 == b))]

Do you see any reason why this should not work?

[#9]

(It looks like a and b are column variables, and I think that comparing column with a value would always return false.) <<wrong

Edit: On second thought it should work fine as long as the criteria is true for at least one row in each combination of by-categories (I was first thinking jsl, not column formula context where "current row" is assumed). Good solution!