LOOKUP

Category: Transform / Basic

Description

This action looks up values from a specified column in another table and if found, a returns value from the same row but different column of that table.

When the value is not found, three options are available:

Keep old value
Leave cell empty
Put some predefined (default) value

Use cases

Used to replace common misspellings with the correct value.
Used to replace abbreviations with the full value.
Used to replace numeric codes with descriptive text values.

Action settings

Setting	Description
Lookup table	Select the dataset that will serve as the lookup table.

Mode settings

Setting	Description
Append new column	A new column (named "New name") will be created in the current dataset to hold the result of the lookup.
Replace in (column)	Upon a successful match in the lookup table, the return value will replace the original value in the current dataset.

Columns that should match settings

Setting	Description
This table	Select the column in the current dataset containing values that will be matched in the foreign dataset.
(Look up in column)	The column in the second dataset that will be used to find matching values in the current dataset.
Return from	Select the column in the second table from which values will be returned based on a match in the lookup column.

No conditions met options

Setting	Description
Keep original value	If a value does not have a match in the lookup table, keep the original value.
Make empty	If a value does not have a match in the lookup table, make the value empty.
Default to	Options: it's an expression or it's a text or number. Enter either an expression to be evaluated or a static value to be used in cases where a value is not found in the lookup table.

Examples

Example: Substitute all instances of the province name variations with "Quebec" for consistency.

Source table 1: Canadian Olympic Champions 2014

Gold medalist	Province	Sport
Dara Howell	Ontario	women’s slopestyle
Justine Dufour-Lapointe	Quebec	women’s moguls
Kaillie Humphries	Alberta	women’s bobsleigh
Alex Bilodeau	QC	men’s moguls
Heather Moyse	P.E.I.	women’s bobsleigh
Charles Hamelin	Que.	men’s short-track

Source table 2: Substitution lookup table

Old name	New name
Quebec	Quebec
QC	Quebec
Que.	Quebec

Action parameters:

Replace value in column: Province
Using table: Source table 2
Look up in column: Old name
Replace with values from column: New name
When not found: Keep original value

Result table:

Gold medalist	Province	Sport
Dara Howell	Ontario	women’s slopestyle
Justine Dufour-Lapointe	Quebec	women’s moguls
Kaillie Humphries	Alberta	women’s bobsleigh
Alex Bilodeau	Quebec	men’s moguls
Heather Moyse	P.E.I.	women’s bobsleigh
Charles Hamelin	Quebec	men’s short-track

Alternate result:
If "Append new column" had been selected instead of "Replace value", above, the following dataset would be the output:

Gold medalist	Province	Sport	New name
Dara Howell	Ontario	women’s slopestyle	Ontario
Justine Dufour-Lapointe	Quebec	women’s moguls	Quebec
Kaillie Humphries	Alberta	women’s bobsleigh	Alberta
Alex Bilodeau	QC	men’s moguls	Quebec
Heather Moyse	P.E.I.	women’s bobsleigh	P.E.I.
Charles Hamelin	Que.	men’s short-track	Quebec

Community examples

How to look up and replace a string of text (Project; Module: Main; Group: Tab 1; Table: Table 3; Action position: 4)
Joining different pairs of value equivalences into “global equivalences” (Project; Module: Group; Group: Tab 1; Table: Result;
Action position: 2)
Search, remove, and shift (Project; Module: Main; Group: Regrouping; Table: ExtractingData.xlsx; Action position: 12)

EasyMorph Help

Table of Contents