{{ transformations:LookupAction.png}}
====== LOOKUP ======
Category: Transform / Basic\\

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

\\
=====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.
  * Used to map values to aggregate categories.

\\ 
=====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 #1====
>Substitute all abbreviations of Quebec with "Quebec". 

===Before (source table)===
Table 1: Canadian Olympic Champions 2014
^Gold medalist  ^Province  ^Sport  ^
| Alex Bilodeau  |QC  |moguls  |
| Dara Howell  |Ontario  |slopestyle  |
| Charles Hamelin  |Que.  |short-track  |

Table 2: Quebec abbreviation lookup
^Old name  ^New name  ^  
| QC  |Quebec  | 
| Que.  |Quebec  | 

===After (result table)===
^Gold medalist  ^Province  ^Sport  ^
| Alex Bilodeau  |**Quebec**  |moguls  |
| Dara Howell  |Ontario  |slopestyle  |
| Charles Hamelin  |**Quebec**  |short-track  |

===Action parameters===
>Lookup table: Table 2
>Columns that should match: Province (Table 1), Old name (Table 2)
>Return from: New name
>When not matched: Keep original value

\\
====Example #2====
>Map individual employees to their departments based on their job titles. 

===Before (source table)===
Table 1: Employee list
^Name  ^Job title  ^
| Luca  |Tech 1  |
| Marta |COO  |
| Sam  |CFO  |
| Sveta  |Tech 3  |
| Mike  |Tech 2  |
| Jean  |HR  |

Table 2: Job title to Department mapping
^Title  ^Dept name  ^  
| CFO  |C-Suite  | 
| COO  |C-Suite  | 
| Tech 1  |IT  | 
| Tech 2  |IT  | 
| Tech 3  |IT  | 

===After (result table)===
^Name  ^Job title  ^Dept name ^
| Luca  |Tech 1  |**IT**  |
| Marta |COO  |**C-Suite**  |
| Sam  |CFO  |**C-Suite**  |
| Sveta  |Tech 3  |**IT**  |
| Mike  |Tech 2  |**IT**  |
| Jean  |HR  |**HR**  |

===Action parameters===
>Lookup table: Table 2
>Columns that should match: Job title (Table 1), Title (Table 2)
>Return from: Dept name
>Mode: Append new column
>When not matched: Keep original value

\\ 
=====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]]