Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Is there a JMP function that is equivalent to the Excel VLOOKUP function?

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 14, 2012 7:50 AM
(7092 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I figured out a solution that works:

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

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I figured out a solution that works:

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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]] ); |

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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