{{ transformations:LookupAction.png}}
====== 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====
  * [[https://community.easymorph.com/t//1551/2|How to look up and replace a string of text]] ([[https://community.easymorph.com/uploads/short-url/dvCtD7gg0AHUtXZ7tmQmuHflSYU.morph|Project]]; Module: //Main//; Group: //Tab 1//; Table: //Table 3//; Action position: //4//)
  * [[https://community.easymorph.com/t//1837/4|Joining different pairs of value equivalences into “global equivalences”]] ([[https://community.easymorph.com/uploads/short-url/1TpBXIjf6r4auDHlOBejKh2rSTw.morph|Project]]; Module: //Group//; Group: //Tab 1//; Table: //Result//;\\ Action position: //2//)
  * [[https://community.easymorph.com/t//2060/2|Search, remove, and shift]] ([[https://community.easymorph.com/uploads/short-url/cCqOTak9SLKPXbityR5IAlgULJa.morph|Project]]; Module: //Main//; Group: //Regrouping//; Table: //ExtractingData.xlsx//; Action position: //12//)

\\ 
=====See also=====
  * [[transformations:merge|Merge another table]]
  * [[transformations:replacecolumns|Modify columns(s)]]
  * [[transformations:rule|Rule]]