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
hacketkm
Level III

Is there a JMP function that is equivalent to the Excel VLOOKUP function?

Is there a JMP function that is equivalent to the Excel VLOOKUP function?

I have a data table which includes a column of parts and a separate table with each part and it's corresponding price. In Excel I can easily use VLOOKUP to match the part with it's price.

Can I do this in JMP? Can I do it in a JMP script?

1 ACCEPTED SOLUTION

Accepted Solutions
hacketkm
Level III

Re: Is there a JMP function that is equivalent to the Excel VLOOKUP function?

I figured out a solution that works:

 

dt1 << Join( With( <dt2>), By Matching Columns(col1 == col2));

View solution in original post

3 REPLIES 3
hacketkm
Level III

Re: Is there a JMP function that is equivalent to the Excel VLOOKUP function?

I figured out a solution that works:

 

dt1 << Join( With( <dt2>), By Matching Columns(col1 == col2));
satish
Level III

Re: Is there a JMP function that is equivalent to the Excel VLOOKUP function?

Below is one other way:

selrowno = dtcclimits << get rows where( :ID == ID );
IUCL = Num( Column( dtcclimits, "_UCL" )[selrowno[1]] );
jorgeramosdealm
Level III

Re: Is there a JMP function that is equivalent to the Excel VLOOKUP function?

The closest that I have found is update. The difference between join and update is that join creates a new table whereas update adds the columns to the first table.

Col1 from table dt is the reference for col1 from table dt2.

Col2 is the columns with the values that we want to add to table 1.

dt << update (with (dt2), by matching columns (dt:col1==dt2:col1), add columns from update table(:col2));