Lookup
Lookup is used to match/compare two tables against some common field(s) and extract the results pertaining to these fields.
SheetKraft contains the Lookup option which can be accessed by the Lookup Button present in the SheetKraft Toolbar ( See figure)
Case 1: Basic Example
The following guidelines describe how Lookup is used.
Step 1
Click on the Lookup Button and select the Lookup option to open this dialogue box.
Select the data which you want to match against the other sheet. These are called the Key values. There are two ways by which you can select the data: 1)By manually selecting the data from the sheet. (By dragging over the data) 2)By entering the cell indices of the starting and ending block, for example, $A$1:$G$800.
You can select Change sheet if you want to select data from some other sheet. Also, click on the My data has headers checkbox if you have selected the headers with the data.
After selecting the data, click on OK.
Step 2
Another dialogue box will open asking you to select the range containing Mappings for the Key values you earlier selected. Select the data against which you want to match the Key Values. Use Change sheet option to select data from another sheet. After selecting the data, click OK.
Step 3
The dialogue box shown below, opens:
In the Match Criteria section, you can input the field(s) using which you want to match the two tables. The first argument refers to column in lookup table and second argument refers to the key value.
You can also use Multiple matching criteria. To add a second criterion click on the + sign.
By default, multiple conditions are linked by AND ie. matching is done for all conditions and only results, satisfying all conditions simultaneously, are displayed.
SheetKraft provides the option to use Nested conditions and a variety of conditional operators to define custom Matching criteria.
To use advanced options, click on Advanced.
The layout changes to (see figure):
Notice that instead of the equal to operator we can now select from a variety of operators (see figure below).
The table explains the functionality of the various operators present.
| Operator | Function |
|---|---|
| Equal to | This operator compares whether two entities are equal. This operator works on textual arguments too. Requires the user to supply a value with which data are compared. |
| Greater than equal to | This operator compares whether one entity is either greater than or equal to another entity. This operator doesn't work on textual arguments. Requires the user to supply a value with which data are compared. |
| Greater than | This operator compares whether one entity is greater than another entity. This operator doesn't work on textual arguments. Requires the user to supply a value with which data are compared. |
| Less than equal to | This operator compares whether one entity is either lesser than or equal to another entity. This operator doesn't work on textual arguments. Requires the user to supply a value with which data are compared. |
| Less than | This operator compares whether one entity is lesser than another entity. This operator doesn't work on textual arguments. Requires the user to supply a value with which data are compared. |
| Is error | Condition is satisfied if the field in consideration contains some error? |
| Not error | Condition is satisfied if the field in consideration does not contain any error. |
| Is #N/A | Condition is satisfied if the field in consideration contains some null value(#N/A) |
| Not #N/A | Condition is satisfied if the field in consideration does not contain any null value(#N/A) |
| Is logical | Condition is satisfied if the field in consideration contains a logical expression/vale (TRUE/FALSE) |
| Is blank | Checks whether a field is blank(empty). Requires no user input value. |
| Is text | Checks whether a field contains only textual data. Requires no user input value. |
| Is number | Checks whether a field contains only numerical data. Requires no user input value. |
| Contains substring | Checks whether a text contains another smaller text within itself. For example, ant is a substring of elephant. The substring is supplied by the user. |
| Does not contain substring | Condition is satisfied if the given text does not contain the substring provided by the user. |
| Text starts with | Checks whether the start of a text contains a string. The string has to be supplied by the user. |
| Text does not start with | Condition is satisfied if the text in a selected field does not start with the text provided by the user. |
| Text ends with | Checks whether the end of a text contains a string. The string has to be supplied by the user. |
| Text does not end with | Condition is satisfied if the text in a selected field does not end with the text provided by the user. |
| Is contained in string | Checks whether a text (supplied by user) is contained in another longer text. |
| Is the beginning of string | Checks whether the user supplied text is the beginning of a text in the data. |
| Is the ending of the string | Checks whether the user supplied text is the end of a text in the data. |
| Text matches regex | Checks whether the data in a field matches the regular expression input by the user. |
| Text does not match regex | Satisfied when the data in the selected field does not match with the regular expression input by the user. |
| Belongs to range | Checks whether a value lies in the range given by user. |
| Does not belong to range | Checks whether a value lies in the range given by user. Condition satisfied if the value dosen't lie in the range. |
In the Advanced matching criteria, multiple conditions can be linked using AND or OR. And NOT option can be enabled by clicking in the vertical bar.
Nested conditions comprise condition statements contained within the definition of other condition statements.
There is a Plus sign on the right hand side of the conditions. To form a nested condition, click on the Plus sign besides the condition you want to nest.
Select the nested condition. Example of a nested criteria :
Step 4
After setting the Match Criteria,we need to define which columns you want to view in the result. In the section named Report the following columns, click the + sign to select a column.
Clicking on the + sign opens a menu that lists all the fields in the Lookup table. Select the required field.
Step 5
After you have added field(s) to report, you need to select the Function which you want to operate on the data.
The table below explains each of these functions:
| Function | Use |
|---|---|
| Sum | This function simply adds all the values pertaining to one entity. For example, in our selected example Sum adds the profit values belonging to one customer. |
| Count | This function counts the number of data items belonging to one entity. For example, in our selected example Count gives the number of profit values belonging to one customer. |
| Average | This function calculates the mean of all the data values pertaining to one entity. For example, in our selected example Average gives the mean of all the profit values belonging to one customer. |
| Max | This function finds the maximum value among various data items belonging to one entity. For example, in our selected example Max gives the maximum profit value belonging to one customer. |
| Min | This function finds the minimum value among various data items belonging to one entity. For example, in our selected example Min gives the minimum profit value belonging to one customer. |
| Std Dev | This function calculates the standard deviation of the data items belonging to one entity. |
| Count numbers | This function counts the number of numerical data present in data items belonging to one entity. |
| Count unique | It counts the number of unique values in the data items belonging to one entity. |
| All Items | It displays all the items of the selected field belonging to one entity. |
| Unique Items | It displays all the unique items of the selected field belonging to one entity. |
| First | It displays the first data value of the selected field belonging to one entity. |
| Last | It displays the last data value of the selected field belonging to one entity. |
If there is no match, the result for the corresponding row will be N/A. You can also give a custum value in case of no match, by specifying the value under the If No Match header. You can also give a custom name to the column being formed (under Header).
Step 6
After you have selected the required data fields, click on Next. You will be asked where to save the result
To save the data in the Existing WorkSheet, you can input the index of the starting cell where you want to copy the data. Or you can save the data in a New Worksheet.
Step 7
After entering your choice, click Finish.
Case 2: Lookup Matching Condition: Match Only Once
This will match the keys with the lookup table only once. This will return only unique matches. To understand this, lets look at an example.
Step 1
Suppose we have a dataset as shown below(blue) and we made a column with key values to look for(red). We select the range containing key values first(red) then lookup table range containing mappings containing key values(blue).
Step 2
After selecting the data and lookup table, define the matching criteria as Date in both tables as described in Case 1. Tick on Match only once.
Step 3
Select the columns to be reported(shown below) and hit Next.
We will observe that the obtained output contains some values as #N/A. This is because some of the keys are repeating so when we are using Match only once, keys are matched exhaustively to the lookup table. So some of the repeating keys return #N/A.
Second image shows the output if we didn't tick on the Match only once, which maps keys to lookup repetitively if key repeats.
Case 3: Many To One Lookup
This functionality will match many keys to one lookup and will give the potential mapping combinations. To understand this, lets look at an example.
Step 1
Below are two tables shown, key table(Red) and Lookup table(Blue). The key table contains field amount and we need to find possible combinations of key values whose sum of amount equals to the field sum in the lookup table.
Step 2
After selecting the data and lookup table, tick on Match only once. Then a new check box for Many to one lookup appears.
Step 3
Tick on Many to one lookup.
Step 4
The dropdown menu in the first parameter in many to one match criteria contains all fields from key table. The second parameter contains all fields from lookup table.
If the user do not want to add Additional matching criteria the corresponding checkbox can be unchecked.
If the user want to limit the combinations of key values to the common value of a field in key table check the Restrict matches to the common value of checkbox.
Then a field appears and you can select a field from key table from the dropdown window.
Step 5
Select the required matching criteria and report columns and click Next. The following image shows the result.
