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) Ribbon

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. Data

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. Data

Step 3

The dialogue box shown below, opens: Data

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. Data

Data

You can also use Multiple matching criteria. To add a second criterion click on the + sign. Data

By default, multiple conditions are linked by AND ie. matching is done for all conditions and only results, satisfying all conditions simultaneously, are displayed. Data

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. Data

The layout changes to (see figure): Data

Notice that instead of the equal to operator we can now select from a variety of operators (see figure below). Data

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. Data

Data

Data

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. Data

Select the nested condition. Example of a nested criteria : Data

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. Data

Clicking on the + sign opens a menu that lists all the fields in the Lookup table. Select the required field. Data Data

Step 5

After you have added field(s) to report, you need to select the Function which you want to operate on the data. 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). Data

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. Data

Step 7

After entering your choice, click Finish. Data

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). Data

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. Data

Step 3

Select the columns to be reported(shown below) and hit Next. Data

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. Data

Data

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. Data

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. Data

Step 3

Tick on Many to one lookup. Data

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. Data

Data

If the user do not want to add Additional matching criteria the corresponding checkbox can be unchecked. Data

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. Data

Then a field appears and you can select a field from key table from the dropdown window. Data

Step 5

Select the required matching criteria and report columns and click Next. The following image shows the result. Data