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
- :
- How do I implement an excel like VLOOKUP using JSL?

- 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
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Aug 27, 2018 2:45 AM
(4239 views)

How to find the students' sports names in the "big class families.jmp" table according to the students' names in the "Big Class.jmp" table.

and add columns in the "Big Class.jmp" table to display.Thank you very much!

```
d2 = Open( "$SAMPLE_DATA/big class families.jmp" );
d1 = Open( "$SAMPLE_DATA/Big Class.jmp" );
```

1 ACCEPTED SOLUTION

Accepted Solutions

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

Update() by matching columns works well for this task. Either manually (Tables Menu) or by script.

Example:

```
d2 = Open("$SAMPLE_DATA/big class families.jmp");
d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d1 << Update(
With(d2),
Match Columns(:name = :name),
Add Columns from Update table(:sports)
);
```

7 REPLIES 7

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

Update() by matching columns works well for this task. Either manually (Tables Menu) or by script.

Example:

```
d2 = Open("$SAMPLE_DATA/big class families.jmp");
d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d1 << Update(
With(d2),
Match Columns(:name = :name),
Add Columns from Update table(:sports)
);
```

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

Re: How do I implement an excel like VLOOKUP using JSL?

This is very useful. Thank you very much!

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

Re: How do I implement an excel like VLOOKUP using JSL?

Please continue your guidance:how to extract the value by matching both names simultaneously, like the offset function in excel.Thank you very much!

```
d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d2 = New Table( "offset",
Add Rows( 8 ),
New Column( "name",
Character,
"Nominal",
Set Values( {"MARION", "MARION","MARION","MARION", "JUDY", "JUDY", "JUDY", "JUDY"} )
),
New Column( "obj",
Character,
"Nominal",
Set Values( {"sex", "age", "weight", "height", "sex", "age", "weight", "height"} )
)
);
```

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

Re: How do I implement an excel like VLOOKUP using JSL?

It looks like you want to stack columns. Explore the *Stack() *command. However, in your example you have stacked columns of different data types (Character and numeric), which will not work unless data types are harmonized first.

```
d1 = Open("$SAMPLE_DATA/Big Class.jmp");
For(i = 1, i <= N Col(d1), i++,
Column(d1, i) << data type("Character")
);
d1 << Stack(Columns(:age, :sex, :height, :weight));
```

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

Re: How do I implement an excel like VLOOKUP using JSL?

Thank you. That's a very efficient way. I'll keep that in mind.

But what if I only needed code to extract the data for the eight line names I gave?Want to learn one more way.

It's bothering you，thank you!

But what if I only needed code to extract the data for the eight line names I gave?Want to learn one more way.

It's bothering you，thank you!

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

Re: How do I implement an excel like VLOOKUP using JSL?

There are several ways. One is to simply delete unwanted rows from the stacked table.

```
d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d2 = d1 << Stack(Columns(:age, :sex, :height, :weight));
d2 << delete rows(d2 << get rows where(:name != "MARION" & :name != "JUDY"));
```

Or just make a subset. The example below is without stacking (in JMP, it's rarely a good idea to mix widely different variables in the same column).

```
d1 = Open("$SAMPLE_DATA/Big Class.jmp");
d1 << Subset(rows(d1 << get rows where(:name == "MARION" | :name == "JUDY")));
```

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

Re: How do I implement an excel like VLOOKUP using JSL?

a good way，thank you!