Description
Looks up the values of a column in the top input dataset in the bottom input dataset and puts the result in a new column.
Examples
Lookup 'Category Name' in a second dataset using 'Category ID':
Fuzzy lookup of company stock 'Symbol' by 'Name':
Lookup the staff assigned to each day:
Inputs
Two.
Options
•Place the dataset you want to modify as the top input and the dataset you want to lookup values from as the bottom input.
•Click Explore Keys... to compare key values in the 2 datasets.
•Uncheck automatic new column name to set a name for the newly created column in New column name.
•Select Top key column for the column whose values you wish to lookup.
•Select Bottom key column for the column that matches the lookup in the bottom dataset.
•Select Bottom value column for the column that contains the values.
•Set Matching to how you wish to match values with the lookup table.
oExact, Not exact and the various contains, starts with and ends with matches, match as text. E.g. date values 1/1/2024 and 01/01/2024 are not considered an exact match.
oEqual, Not equal and the various greater than or greater than equal matches, match date and numeric columns using date and numeric comparisons (respectively). E.g. date values 1/1/2024 and 01/01/2024 are considered equal.
oExact match is fast. Other options are likely to be much slower.
•Set Closeness to how close a fuzzy match has to be, to be considered a match. E.g. set it to 80% to make 2 values that are 80% the same a match. For Fuzzy matching only.
•Set Bottom values used to:
oAll if you want to use all matches. Duplicate values are only shown once.
oFirst if you want use the first match in Bottom lookup column. For Exact matching only.
oAll best if you want to use the best matches. If there are multiple values that are equally good, use all of them. Duplicate values are only shown once. For Fuzzy matching only.
oFirst best if you want to use the best match. If there are multiple values that are equally good, use the first one. For Fuzzy matching only.
•Set Delimiter to the delimiter you want to use to separate multiple values returned for Bottom values used of All or All best.
•Set If no match to Use not match value or Leave unchanged, depending on what you want to do for values in Top lookup column that do not match in Bottom lookup column.
•Set No match value to the value you want to use for values in Top lookup column that do not match in Bottom lookup column when If no match is set to Use no match value. You can use a column variable to use values from the top dataset.
•Check case sensitive to use case sensitive matching for keys.
Notes
•Easy Data Transform will try to guess sensible default values for Top lookup column, Bottom lookup column and Bottom value column.
•Bottom values used is only important if there are duplicate values in the Bottom lookup column.
•If the top input has a header, this will be used for the output.
•If you want to see what bottom dataset keys are matched to each top dataset key, you can set Bottom value column to the same column as Bottom key column.
•Exact and Not exact match as text. So date values “1/1/2024” and “01/01/2024” are not considered an exact match.
•Equal and Not equal match date and numeric columns using date or numeric comparisons. So date values “1/1/2024” and “01/01/2024” are considered equal.
•Text comparisons are whitespace sensitive. You can use Whitespace to remove whitespace before the lookup.
•Exact match is fast. Other options are slower. Fuzzy matching is much slower.
•If you want to lookup values in multiple columns, use Concat Cols to join several columns together to form new columns.
•The new column is added at the right end. You can change the column order with Reorder Cols and the column name with Rename Cols.
•Warnings are shown in the Warnings tab for:
oValues that cannot be found in the bottom dataset (misses).
oValues that occur more than once in the bottom dataset (duplicates), if Bottom values used is First.
See also
•If
•Join